ansaurus

Question

Answer 1

+1 A:

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 2010-09-27 17:32:16

In Server roles, im in public and the sysadmin server role is not checked ...

pdiddy 2010-09-27 17:35:05

Run these `SELECT SUSER_NAME()` and `SELECT USER_NAME()` and tell me your output.

Dustin Laine 2010-09-27 18:20:58

the first one is my user and the second one is dbo ....

pdiddy 2010-09-27 21:45:56

Is there a reason for the difference ?

pdiddy 2010-09-28 13:04:05

Answer 2

+2 A:

Couple of options:

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).
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.
Check that the database selected in the "Available Databases" drop down (upper left, to the left of the Execute button) is correct.
Use three name notation when specifying table (and view) references:
```
SELECT * 
  FROM [your_db].[your_schema].table_name
```

OMG Ponies 2010-09-27 18:19:08

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 2010-09-27 19:15:38

@pdiddy: I added that a synonym is another consideration. Sorry, I missed that you didn't want to use name notation.

OMG Ponies 2010-09-27 19:24:58

for option 1, how do I check for the context .. ?

pdiddy 2010-09-27 20:10:16

@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 2010-09-27 20:18:49

ansaurus

tags:

views:

answers:

SQL server schema and default schema

related questions