SQL Server 2008: How do I change the default schema of the dbo?

Question

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!

Answer 1

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      login.sid = d.owner_sid
join    sys.server_principals as login
on      user.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.

Answer 2

Database User dbo is mapped to logins of the sysadmin fixed server role. I believe it is because of this that you can't alter the default schema for db user dbo.

The real question is, why do you want to change the default schema of dbo to something else? With built-in specifics (fixed roles, fixed logins/users), you really should be minimizing how must you customize them.

If you don't want users to have a default schema of dbo, then maybe you should reconsider your logins and database users so they don't map to max rights objects.