Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Oct 9 '11 at 21:23

This question came from our site for professional and enthusiast programmers.

4  
Why do you want to do this? –  gbn Oct 9 '11 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. –  Thomas Stringer Oct 9 '11 at 19:36
    
@Surfer513: Did you mean to to address @user962143? –  gbn Oct 9 '11 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. –  Thomas Stringer Oct 9 '11 at 20:10

2 Answers 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.

share|improve this answer
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 Oct 9 '11 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 Oct 9 '11 at 21:04

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.