MS SQL Server – Change Default Schema for users

MS SQL Server – Change Default Schema for users

Starting with MS SQL Server v2005, Microsoft introduced Schemas.  When converting a MS SQL Server 2005 database from MS SQL Server 2000 and below or adding users using sp_adduser; who may mistakenly create new schemas and assign users to schemas bearing their name.

Identify those users

select 
           tblPrincipal.[name]
         , tblPrincipal.type
         , tblPrincipal.default_schema_name
from   sys.database_principals tblPrincipal
-- U is for SQL User
-- S is for OS User
where  tblPrincipal.type in ('U', 'S')
-- only list those users not currently assigned to dbo
and    tblPrincipal.default_schema_name not in ('dbo')

Prepare SQL for identifying and changing those users

select 'ALTER USER [' + name + '] with default_schema = [dbo]'
from   sys.database_principals
where  type in ('U', 'S')
and    default_schema_name not in ('dbo')

Execute the generated SQL


Suggested Reading

1] Change schema name on Tables and Stored procedures in SQL Server 2005

http://weblogs.asp.net/steveschofield/archive/2005/12/31/change-schema-name-on-tables-and-stored-procedures-in-sql-server-2005.aspx

2] Alter user

http://msdn.microsoft.com/en-us/library/ms176060.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s