SQL Server added schema support in SQL Server 2005, but there are still some issues. There appears to be no direct way to set the implicit schema for a session. It would be nice to have something like:
USE database.schema
Alas, no such functionality exists (yet). Someone has sent Microsoft a suggestion along these lines.
There are a couple of workarounds. First, you can change the default schema for a user in a particular database using the User Mapping page in Management Studio. Equivalently, you could issue commands like:
USE myDatabase
ALTER USER myUserId WITH DEFAULT_SCHEMA = mySchema
To change the default schema for a particular session, you could try:
EXECUTE AS USER = 'mySchemaUser'
where mySchemaUser is a user who is already mapped to the desired schema. Since this approach uses impersonation, you might run into a rights issue. A common case is to target the dbo schema (through the dbo user). The exhibited command works when the original user has the db_owner role.
