SQL Server Table Owner - change the default

Question

Is there something in SQL Server similar to USE (to switch databases) that can control the owner prefix that is used for tables?

For example, we have an application that insists on creating tables "theServiceAccount.TheTableName" ; What we really want is to force it to put the table names under dbo... so "dbo.TheTableName" . We don't have a good way to change the SQL that the application runs (it is vended), other than a hook when it starts up that allows us to run some SQL. So it would be great if we could run a sql at that point which would make subsequent create table (or other operations) default to dbo instead of the service account being used.

I do realize that the create table syntax allows one to specify the owner, but that doesn't seem to be an option at this point. From what I can tell, the SQL this application generates never specifies the owner; it just has the table name in the SQL it runs.

Thanks!

Answer 1

I believe you can do this by creating a user with the default schema you want and then using the EXECUTE AS statement (see http://msdn.microsoft.com/en-us/library/ms181362.aspx)

In your example you could create a user (or use dbo, not advised) called 'specialDBO' that has their default schema set to dbo. Then you have something like this:

     USE [myfabdb];
     EXECUTE AS USER = 'speicalDBO';

     ... blah blah blah...

     REVERT;

Remember, you can't have the USE statement after the EXECUTE AS statement.

Answer 2

In 2005, by default each user has their own default schema, unless specified.

This should do what you need:

USE databasename  
ALTER USER theServiceAccount WITH DEFAULT_SCHEMA = dbo

You can also change this via SSMS by looking at the user properties and changing the default schema