CODEWITHSUNDEEP
sql-server
Roy Helmer
Roy Helmer

Reputation: 11

Default_Schema not working

I have the default schema for my user XYZCORP\JShmoe set to 'accounting'.
When I log as XYZCORP\JShmoe and execute SELECT SCHEMA_NAME() it returns "dbo" instead of 'accounting'.

Consequentially this works: 

Select * From accounting.UserInfo

but this doesn't: 

Select * From UserInfo

XYZCORP\JShmoe is not sysadmin.

As a note, the above is on our production server. On our dev server everything seems to be the same (all the login and user properties I can see) but it does work.

Upvotes: 0

Views: 425

Answers (1)

Ben Thul
Ben Thul

Reputation: 32707

As you're finding, default_schema is kind of fragile. My recommendation would be to not rely on that mechanism for object resolution but rather to fully qualify your objects (e.g. accounting.UserInfo instead of UserInfo). That said...

Here are a couple of situations that could explain what you're seeing:

  • The user is a member of the sysadmin group. According to the documentation, members of sysadmin always get dbo as their default schema regardless of database ownership. Check the sys.login_token view to confirm or deny this

  • The user is a member of a Windows group that is itself a database principal (i.e. has an entry in sys.database_principals) and has a default_schema set. The documentation is clear about how resolution works here as well: if a user belongs to such a group, that group's default schema is used. If a user belongs to multiple such groups, the default_schema for group with the lowest principal id is used (emphasis mine). So, even if you have the same groups between your dev and production servers, if they were created in a different order, your results will be different between the two environments. Check the sys.user_token view to see what group memberships the current user has.

So, assuming that you can't take my initial advice of fully qualifying your objects, check the two conditions above.

Upvotes: 1

Related Questions