Change Database Owner

By James|08/04/2011|,

I received the following fun message while attempting to change SQL Server 2008 database properties:

Cannot show requested dialog (sqlMgmt)

Property Owner is not available for database xyz.  This property may not exist for this object, or may not be retrievable due to insufficient access rights.

Running the sp_helpdb query showed there were several SharePoint databases with 'UNKNOWN' listed as the owner.  To fix this, the following query was created and executed against the problem databases:

USE DBNAME
EXEC sp_changedbowner 'DOMAIN\ACCOUNT'

For my query, I used the farm domain account and made it the owner.  Problem resolved.

The new and improved way to change a database owner is to use the ALTER AUTHORIZATION command like so:

--change SomeDatabase owner to 'sa'
ALTER AUTHORIZATION ON DATABASE::SomeDatabase to sa;

 

04/05/14 UPDATE - New job, new fun.  I found several databases where the original database owner no longer worked for the organization and their account was expired/deleted.  When  changing the database owner, you may receive a message stating that the new owner account already exists in the database.  To resolve this, expand the database > Security > Users and delete the user account.  Run the above T-SQL query and all will be right in the world again.

 

Copyright 2011 - 2024 The Lazy IT Admin | All Rights Reserved
menu-circlecross-circle linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram