How to change the ownership of SQL Server or Database Owner permission from one account to other.

Problem: How to change the ownership of SQL Server or Database Owner permission from one account to other.

Solution: A​UT​HO​RI​ZA​TI​ON

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.

Pre-requisites for changing the owner to a login, the login must be one of the below:

·         A SQL Server authentication login.

·         A Windows authentication login representing a Windows user (not a group).

·         A Windows user that authenticates through a Windows authentication login representing a Windows group.

·         A user who is executing this alter statement must be a sysadmin.

Example: In this example, I would like to show we can change the ownership of a database

T-SQL:

ALTER AUTHORIZATION ON DATABASE::Test TO sa;

-- to test the change of the ownership.

SELECT d.name, d.owner_sid, sl.name  

FROM sys.databases AS d 

JOIN sys.sql_logins AS sl 

ON d.owner_sid = sl.sid

where d.name='Test';

OUTPUT:

name	owner_sid	name
Test	0x01	sa