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

         , 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

2] Alter user

Leave a Reply

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

You are commenting using your 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