41

I have a schema define in my database. Except now everytime I do a sql statement I have to provide the schema ... SELECT * FROM [myschema].table

I set the default schema for my user using management studio and also ran the ALTER USER myUser WITH DEFAULT_SCHEMA [myschema] and I still get the invalid object 'table' when writing a query without the schema (SELECT * FROM table)

Is there a way to write SELECT * FROM table without having to specify the schema name all the time?

It's on SQL 2005 using SQL Management Studio.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
pdiddy
  • 6,217
  • 10
  • 50
  • 111

3 Answers3

59

Is the user an SA, if so it will not work, according to the documentation SA users are always defaulted to the dbo schema.

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
  • 5
    Run these `SELECT SUSER_NAME()` and `SELECT USER_NAME()` and tell me your output. – Dustin Laine Sep 27 '10 at 18:20
  • Is there a reason for the difference ? – pdiddy Sep 28 '10 at 13:04
  • This bit of info just helped me out - default schema was being ignored for some reason and now we know why .... – codeulike Sep 01 '11 at 09:40
  • Thanks. This solved my problem. I was facing "object not found" error while doing SQL scrip Migrations. My user was a sysadmin and so all scripts were failing :) – Taher Dec 18 '12 at 10:30
  • @DustinLaine - SELECT SUSER_NAME() == MyUserForLogin, SELECT USER_NAME() = dbo. What does this mean ? Could this be related to the problem I am having here - http://stackoverflow.com/questions/21151693/sql-query-works-in-ssis-ssms-sql-server-but-fails-in-deployment – Steam Jan 16 '14 at 18:02
  • 1
    So the problem was.... to much permissions! Remove sysadmin role. The microsoft style :) – Chris W Dec 30 '14 at 11:39
4

Couple of options:

  1. Is your user listed under Security > Users (in SSMS)? Check the Properties (right click the name), and see if the Default schema is set in the context of the database, rather than the instance (which is what ALTER USER is setting).

  2. Create a synonym for the table you want to reference:

    CREATE SYNONYM table_name  
   FOR [your_db].[your_schema].table_name

    ...which will affect everyone who doesn't use at least two name notation, in the context of that database. Read more about it here. But it is associated ultimately to a schema.

  3. Check that the database selected in the "Available Databases" drop down (upper left, to the left of the Execute button) is correct.

  4. Use three name notation when specifying table (and view) references:

    SELECT * 
  FROM [your_db].[your_schema].table_name
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 2
    My question was about if it was possible to avoid the name notation through some configuration in SSMS .... I'd like to not include the whole db, schema in my query as it can be long. The Available Databases is selected correctly. – pdiddy Sep 27 '10 at 19:15
  • 1
    @pdiddy: I added that a synonym is another consideration. Sorry, I missed that you didn't want to use name notation. – OMG Ponies Sep 27 '10 at 19:24
  • 1
    for option 1, how do I check for the context .. ? – pdiddy Sep 27 '10 at 20:10
  • 1
    @pdiddy: It's based on the database selected in the *Available Databases** drop down in SSMS. Run the `CREATE SYNONYM` statement and then check what's listed under the **Synonyms** node for the database in SSMS--for me, the synonym included the schema name. Might have to **Refresh** the Synonym node to see the newly created synonym. – OMG Ponies Sep 27 '10 at 20:18
3

If you do not want to use "full qualified" SQl names, then you need to avoid creating your tables using any account or role that's not using the "dbo" default schema assigned. Why do you need to change the default schema on the user if you don't plan on using it?

Mitch Stokely
  • 47
  • 1
  • 2
    Why he should use "full qualified" SQL names? For example he want to store some user data in the database. And there can be LOT of users using same program. By schema, he can have own created table. In our programs this going in a same way. The only important is the working default schema! – Jettero Jun 21 '16 at 10:02