... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)


SQL Server vs. Schemas

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

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.

Blog Archive