My default schema is not working….

I ran into an issue the other day where we moved an application database that was using named schemas (other than DBO) to logically organize the tables within the database.  When we moved the database to a new sql server, we mapped the application’s SQL login to the new database (with the default schema set to a named schema within the database and NOT dbo).

The problem is that the application’s connection seemed to be using the dbo schema by default even though the mapped DB user had the correct default schema.  After a little digging, we learned that if a login has the sysadmin role at the server level, then all queries against user database will default to using the dbo schema regardless of what you have set for the default schema in the mapped database user account.

I found the answer here:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a1155e64-e53f-4e20-9b1d-4f31e193b14e/default-schema-not-working?forum=sqlsecurity

but there are countless other links describing this behavior.  Hope it saves someone a little time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s