Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We just copied a SQL 2008 database over to another server, for testing purposes. The typical user that logs in uses some objects in a certain schema. Let's say it's user foo and schema bar.

foo's default schema is bar at both the instance and database level. However, when foo logs in it can't find any objects in the bar schema without using a fully qualified name.

Why wouldn't the default schema work here?

share|improve this question
up vote 10 down vote accepted

Has foo user sysadmin rights on SQL Server? Because if it does then any sysadmin user will have by default assigned schema dbo, independently of what is set in User's properties for a specific database.

So, in order to have a default schema a user needs to have database roles like db_owner, db_datawriter and so on, instead of server role sysadmin.

Tell me if it'snot your case.

share|improve this answer
That was it. Thanks! – John Cromartie Nov 20 '10 at 14:09
You are a god and I bow to your knowledge, I was puzzled until I bumped into this! – Jens Oct 9 '12 at 11:14
Thanks! Made an account here just to upvote you! – Mikael Holmgren Nov 14 '14 at 12:02

Another scenario in which this problem may occur (SQL 2008 R2)...

I had a user linked to a SQL Authentication Login, that I was trying to associate with an existing schema. The Login did not have sysadmin rights, so the above solution did not help.

I eventually figured out that the database was still configured in SQL 2000 compatibility mode. Changing to SQL 2005 resolved the issue.

share|improve this answer

It will assume that the default schema on the new server is dbo; If the schema has not been copied with the database.

Not the solution but perhaps this might help, having a look at the msdn article.

share|improve this answer
We have configured the default schema for foo on the new server. It is set as bar on both the instance user and the database user level. – John Cromartie Nov 18 '10 at 19:00

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.