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!

  • 4
    Why do you want to do this?
    – gbn
    Commented Oct 9, 2011 at 19:24
  • @gbn, I have been playing around with this for the past 15 minutes and my below answer is the only conclusion I can come up with. Even if I am executing as a separate sysadmin login with a separate db_owner user I still get denied. Commented Oct 9, 2011 at 19:36
  • @Surfer513: Did you mean to to address @user962143?
    – gbn
    Commented Oct 9, 2011 at 19:55
  • @gbn no, I did mean to address you. I wanted a sanity check on my theory. :) My pursuit of what is fact, short of finding exact explanation in documentation, could be faulty and I was just looking to see if I had the right idea before I give some bad gouge. Commented Oct 9, 2011 at 20:10

2 Answers 2


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.


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.

  • 1
    Yep, most built-in principals have limited options to change anything. And it is best practice to qualify all objects with schema anyway.
    – gbn
    Commented Oct 9, 2011 at 20:12
  • The owner of a database is not always sa. A user can own a database without being a member of the sysadmin fixed server role
    – Andomar
    Commented Oct 9, 2011 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.