##### RemarksALTER AUTHORIZATION:

• 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 can change database ownership. This replaces sp_changedbowner. Alter any security management scripts using sp_changedbowner - such as Attach database without log file and rename database.

Best practice

An example of ALTER AUTHORIZATION: a database without an owner, may require an owner to be assigned. The login used to create the database or be assigned the database ownership has been dropped. The database does not have an owner.

Using ALTER AUTHORIZATION assigns an owner

1
ALTER AUTHORIZATION ON DATABASE::DatabaseName TO LoginUser;

Examples

Transfer ownership of a table

The following example transfers ownership of table Product to user Linh. The table is located inside schema Samples.

1
2
ALTER AUTHORIZATION ON OBJECT::Samples.Product TO Linh;
GO

The query could also look like the following:

1
2
ALTER AUTHORIZATION ON Samples.Product TO Linh;
GO

If the objects schema is not included as part of the statement, the Database Engine will look for the object in the users default schema.

1
2
ALTER AUTHORIZATION ON Samples TO Linh;
ALTER AUTHORIZATION ON OBJECT::Samples TO Linh;

Transfer ownership of a view to the schema owner

The following example transfers ownership the view ViewProduct to the owner of the schema that contains it. The view is located inside schema Samples.

1
ALTER AUTHORIZATION ON OBJECT::Samples.ViewProduct TO SCHEMA OWNER;

Transfer ownership of a schema to a user

The following example transfers ownership of the schema Samples to user Linh.

1
ALTER AUTHORIZATION ON SCHEMA::Samples TO Linh;

Transfer ownership of an endpoint to a SQL Server login

The following example transfers ownership of endpoint Server1 to Linh. Because the endpoint is a server-level securable, the endpoint can only be transferred to a server-level principal.

Applies to: SQL Server 2008 and later.

1
ALTER AUTHORIZATION ON ENDPOINT::Server1 TO Linh;

Changing the owner of a table

Each of the following examples changes the owner of the Product table in the Samples database to the database user Linh.

1
2
3
4
ALTER AUTHORIZATION ON Product TO Linh;
ALTER AUTHORIZATION ON dbo.Product TO Linh;
ALTER AUTHORIZATION ON OBJECT::Product TO Linh;
ALTER AUTHORIZATION ON OBJECT::dbo.Product TO Linh;

Changing the owner of a database

APPLIES TO: SQL Server 2008 and later, Analytics Platform System (PDW), SQL Database.

The following example change the owner of the Samples database to the login Linh.

1
ALTER AUTHORIZATION ON DATABASE::Samples TO Linh;

Changing the owner of a SQL Database to an Azure AD User

In the following example, an Azure Active Directory administrator for SQL Server in an organization with an active directory named old@linh.work, can change the current ownership of a database Samples and make an AAD user new@linh.work the new database owner using the following command:

1
ALTER AUTHORIZATION ON database::Samples TO [new@linh.work];

Azure AD requires brackets [] around the user name.

That's it for now. Keep coding and enjoy exploring !!!