Sql-server – SQL Server 2008: How to change the default schema of the dbo

schemasql serversql-server-2008

When I attempt to change the default schema of the dbo in SQL Server Management Studio, the ALTER fails and states that the dbo account's schema cannot be changed. Are there any tricks that can make is possible to change the the dbo default schema of "dbo" to "xxx"?

Thank you!

Best Answer

Each database is owned by a server principal (aka login). Inside that database, the owning principal is known as dbo (aka *D*ata*B*ase *O*wner). The database principal (aka user) loses its real name.

For example, for a database I own:

select  [user].name as UserName -- Database specific
,       [login].name as LoginName -- Server wide
from    sys.databases d
join    sys.database_principals as [user]
on      [user].sid = d.owner_sid
join    sys.server_principals as [login]
on      [login].sid = d.owner_sid

Will print "dbo", "Andomar". If you'd change the owner to sa:

exec sp_changedbowner 'sa'

The query would return "dbo", "sa".

You cannot modify the default schema for the user that owns a database. It is always user name dbo with default schema name dbo.

Related Question