How to set the default schema of a database in SQL

问题:

The following line works:

SELECT * FROM [myschema].users

But this does not:

SELECT * FROM users

回答1:

A default schema is user-specific:

USE yourDatabase;
ALTER USER [yourUser] WITH DEFAULT_SCHEMA = myschema;

More information about the ALTER TABLE for SQL 2005 might help you as well.

As this is user-specific, if you have multiple users, you will need to execute this query (on each database) for each user whose default schema you want to update.

It is important to note:

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.

回答2:

You can use:

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;

To set the default schema for user.