sql server - Default_Schema not working -

sql server - Default_Schema not working -

i have default schema user xyzcorp\jshmoe set 'accounting'. when log xyzcorp\jshmoe , execute select schema_name() returns "dbo" instead of 'accounting'.

consequentially works:

select * accounting.userinfo

but doesn't:

select * userinfo

xyzcorp\jshmoe not sysadmin.

as note, above on our production server. on our dev server seems same (all login , user properties can see) work.

as you're finding, default_schema kind of fragile. recommendation not rely on mechanism object resolution rather qualify objects (e.g. accounting.userinfo instead of userinfo). said...

here couple of situations explain you're seeing:

the user fellow member of sysadmin group. according documentation, members of sysadmin dbo default schema regardless of database ownership. check sys.login_token view confirm or deny this

the user fellow member of windows grouping database principal (i.e. has entry in sys.database_principals) , has default_schema set. documentation clear how resolution works here well: if user belongs such group, group's default schema used. if user belongs multiple such groups, default_schema grouping the lowest principal id used (emphasis mine). so, if have same groups between dev , production servers, if created in different order, results different between 2 environments. check sys.user_token view see grouping memberships current user has.

so, assuming can't take initial advice of qualifying objects, check 2 conditions above.

sql-server