Changing the database owner of SQL Server databases

In SQL Server databases, the database owner, or dbo has special rights to the particular database. Sometimes however, it is necessary to change the database owner due to security rights, administrative convenience, and so on.

Although it is easy to view the owner of the database (for example through SQL Server Management Studio, from the properties window of a database), it is not immediately apparent how to change that value.

Luckily, SQL Server provides an easy stored procedure that you can use. This procedure is called sp_changedbowner, and works at least from SQL Server 2000 SP3 onwards. Using the procedure is very easy. For example, say you have Windows Active Directory domain "COMP" and user "Joe" whom you would like to assign as the new database owner. You would then execute following command in the context of the database in question:

EXEC sp_changedbowner 'COMP\Joe'

Of course, you must have the appropriate rights to change the owner. Also note that from SQL Server 2005 and 2008 and onwards, you should consider using the ALTER AUTHORIZATION command instead. The sp_changedbowner procedure will be deprecated in future versions of SQL Server.

Keywords: HowTo