How to Drop a user from a database who owns a dbo schema and is the dbo

SQL Server 2005 Developer Edition:

I can't figure out how to drop a user from a database who is the dbo on a database.

I thought if I created another user to become the new dbo I could then drop the old dbo, but when I try that I get an error saying the user can't be dropped because it owns a dbo schema. Now I have two dbo users I can't get rid of...

How do I get rid of the one I don't want...

Try the following (while logged as a member of sysadmin, other than users you are working on):

1. Create a login
2. While in the context of a database you are changing users for, execute sp_changedbowner 'your_newly_created_login' (see Books Online for detailed help on this SP)
3. Then you should be able to drop users and logins who used to be dbowners

Please let me know if this worked for you or if you need further help.

HTH,
Boris.

|||

Boris B wrote:

Try the following (while logged as a member of sysadmin, other than users you are working on):

1. Create a login
2. While in the context of a database you are changing users for, execute sp_changedbowner 'your_newly_created_login' (see Books Online for detailed help on this SP)
3. Then you should be able to drop users and logins who used to be dbowners

Please let me know if this worked for you or if you need further help.

HTH,
Boris.

Thank you, Boris. I will try that tomorrow and let you know what happens.