SQL Server Table Owner - Changing the Default Value

Question

Is there something in SQL Server like USE (for switching databases) that can control the owner prefix used for tables?

For example, we have an application that insists on creating the tables "theServiceAccount.TheTableName"; We really need to get it to put table names under dbo ... so "dbo.TheTableName". We have no good way to change the SQL that the application starts (it is changed), except for the hook when it starts, which allows us to run some SQL. Therefore, it would be great if we could run sql at this point, which would do the following create table (or other operations) by default for dbo instead of the service account used.

I understand that the syntax of create table allows you to specify the owner, but at the moment this is not like an option. From what I can tell, the SQL created by this application never indicates the owner; it just has the table name in SQL that it runs.

Thanks!

Answer 1

In 2005, by default, each user has their own default scheme, if not specified.

This should do what you need:

USE databasename ALTER USER theServiceAccount WITH DEFAULT_SCHEMA = dbo 

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

Answer 2

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

In your example, you can create a user (or use dbo, not recommended) called "specialDBO", which defaults to the dbo scheme. Then you have something like this:

  USE [myfabdb]; EXECUTE AS USER = 'speicalDBO'; ... blah blah blah... REVERT; 

Remember that after an EXECUTE AS statement, you cannot have a USE statement.