Thursday, October 11, 2012

Changing SQL Server Database Owner


Changing the SQL Server Database Owner

In SQL Server, the owner of the current database can be changed. Any user, a SQL Server login or Microsoft Windows user, who has access to connect to SQL Server can become the owner of a database.

Ownership of the system databases cannot be changed.

ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals.

ALTER AUTHORIZATION ON DATABASE::[my_db] TO [sa];

Reference:

Changing the Database Owner: http://msdn.microsoft.com/en-us/library/ms190909(v=sql.105).aspx

ALTER AUTHORIZATION (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187359(v=sql.105).aspx