Thursday, September 18, 2008

Changing database owner when dbo is associated with a sql or windows account

You may have run into a situation where the dbo role is associated with a sql or a windows account (as you create objects under that identity). The problem? If you attempt to delete the account or remove its mappings (User Mapping under the properties of that account), you will get the following error:

Drop failed for User 'dbo'.

...

Cannot drop the database owner. (Microsoft SQL Server, Error: 15181)




The best way that I have found to go around this is to re-associate dbo with "sa" by using the sp_changedbowner stored procedure:

EXEC sp_changedbowner 'sa'

(Note: make sure you use "USE databaseName" or run the SP in the context of the database)

No comments: