ALTER AUTHORIZATION (Transact-SQL)

Articol
01/17/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Changes the ownership of a securable.

Transact-SQL syntax conventions

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Syntax

-- Syntax for SQL Server
ALTER AUTHORIZATION
    ON [ <class_type>:: ] entity_name
    TO { principal_name | SCHEMA OWNER }
    [;]

<class_type> ::=
     {
      OBJECT | ASSEMBLY | ASYMMETRIC KEY | AVAILABILITY GROUP | CERTIFICATE
    | CONTRACT | TYPE | DATABASE | ENDPOINT | FULLTEXT CATALOG
    | FULLTEXT STOPLIST | MESSAGE TYPE | REMOTE SERVICE BINDING
    | ROLE | ROUTE | SCHEMA | SEARCH PROPERTY LIST | SERVER ROLE
    | SERVICE | SYMMETRIC KEY | XML SCHEMA COLLECTION
     }

-- Syntax for SQL Database

ALTER AUTHORIZATION
    ON [ <class_type>:: ] entity_name
    TO { principal_name | SCHEMA OWNER }
    [;]

<class_type> ::=
     {
    OBJECT | ASSEMBLY | ASYMMETRIC KEY | CERTIFICATE
     | TYPE | DATABASE | FULLTEXT CATALOG
     | FULLTEXT STOPLIST
     | ROLE | SCHEMA | SEARCH PROPERTY LIST
     | SYMMETRIC KEY | XML SCHEMA COLLECTION
     }

-- Syntax for Azure Synapse Analytics and Microsoft Fabric

ALTER AUTHORIZATION ON
     [ <class_type> :: ] <entity_name>
     TO { principal_name | SCHEMA OWNER }
    [;]

    <class_type> ::= {
    SCHEMA
     | OBJECT
    }

    <entity_name> ::=
    {
    schema_name
     | [ schema_name. ] object_name
    }

-- Syntax for Parallel Data Warehouse

ALTER AUTHORIZATION ON
     [ <class_type> :: ] <entity_name>
     TO { principal_name | SCHEMA OWNER }
    [;]

<class_type> ::= {
    DATABASE
     | SCHEMA
     | OBJECT
    }

<entity_name> ::=
    {
    database_name
     | schema_name
     | [ schema_name. ] object_name
    }

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Arguments

<class_type> Is the securable class of the entity for which the owner is being changed. OBJECT is the default.

Class	Product
OBJECT	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).
ASSEMBLY	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.
ASYMMETRIC KEY	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.
AVAILABILITY GROUP	Applies to: SQL Server 2012 and later.
CERTIFICATE	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.
CONTRACT	Applies to: SQL Server 2008 (10.0.x) and later.
DATABASE	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database. For more information,see ALTER AUTHORIZATION for databases.
ENDPOINT	Applies to: SQL Server 2008 (10.0.x) and later.
FULLTEXT CATALOG	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.
FULLTEXT STOPLIST	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.
MESSAGE TYPE	Applies to: SQL Server 2008 (10.0.x) and later.
REMOTE SERVICE BINDING	Applies to: SQL Server 2008 (10.0.x) and later.
ROLE	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.
ROUTE	Applies to: SQL Server 2008 (10.0.x) and later.
SCHEMA	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).
SEARCH PROPERTY LIST	Applies to: SQL Server 2012 (11.x) and later, Azure SQL Database.
SERVER ROLE	Applies to: SQL Server 2008 (10.0.x) and later.
SERVICE	Applies to: SQL Server 2008 (10.0.x) and later.
SYMMETRIC KEY	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.
TYPE	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.
XML SCHEMA COLLECTION	Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database.

entity_name Is the name of the entity.

principal_name | SCHEMA OWNER Name of the security principal that will own the entity. Database objects must be owned by a database principal; a database user or role. Server objects (such as databases) must be owned by a server principal (a login). Specify SCHEMA OWNER as the *principal_name- to indicate that the object should be owned by the principal that owns the schema of the object.

Remarks

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.

Important

Beginning with SQL Server 2005 (9.x), a user can own an OBJECT or TYPE that is contained by a schema owned by another database user. This is a change of behavior from earlier versions of SQL Server. For more information, see OBJECTPROPERTY (Transact-SQL) and TYPEPROPERTY (Transact-SQL).

Ownership of the following schema-contained entities of type "object" can be transferred: tables, views, functions, procedures, queues, and synonyms.

Ownership of the following entities cannot be transferred: linked servers, statistics, constraints, rules, defaults, triggers, Service Broker queues, credentials, partition functions, partition schemes, database master keys, service master key, and event notifications.

Ownership of members of the following securable classes cannot be transferred: server, login, user, application role, and column.

The SCHEMA OWNER option is only valid when you are transferring ownership of a schema-contained entity. SCHEMA OWNER will transfer ownership of the entity to the owner of the schema in which it resides. Only entities of class OBJECT, TYPE, or XML SCHEMA COLLECTION are schema-contained.

If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.

Caution

In SQL Server 2005 (9.x), the behavior of schemas changed from the behavior in earlier versions of SQL Server. Code that assumes that schemas are equivalent to database users may not return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements has ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In a database in which any of these statements has ever been used, you must use the new catalog views. The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005 (9.x). For more information about catalog views, see Catalog Views (Transact-SQL).

Also, note the following:

Important

The only reliable way to find the owner of an object is to query the sys.objects catalog view. The only reliable way to find the owner of a type is to use the TYPEPROPERTY function.

Special Cases and Conditions

The following table lists special cases, exceptions, and conditions that apply to altering authorization.

Class	Condition
OBJECT	Cannot change ownership of triggers, constraints, rules, defaults, statistics, system objects, queues, indexed views, or tables with indexed views.
SCHEMA	When ownership is transferred, permissions on schema-contained objects that do not have explicit owners will be dropped. Cannot change the owner of sys, dbo, or information_schema.
TYPE	Cannot change ownership of a TYPE that belongs to sys or information_schema.
CONTRACT, MESSAGE TYPE, or SERVICE	Cannot change ownership of system entities.
SYMMETRIC KEY	Cannot change ownership of global temporary keys.
CERTIFICATE or ASYMMETRIC KEY	Cannot transfer ownership of these entities to a role or group.
ENDPOINT	The principal must be a login.

ALTER AUTHORIZATION for databases

For SQL Server

Requirements for the new owner: The new owner principal must be one of the following:

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.

Requirements for the person executing the ALTER AUTHORIZATION statement: If you are not a member of the sysadmin fixed server role, you must have at least TAKE OWNERSHIP permission on the database, and must have IMPERSONATE permission on the new owner login.

For Azure SQL Database

Requirements for the new owner: The new owner principal must be one of the following:

A SQL Server authentication login.
A federated user (not a group) present in Microsoft Entra ID.
A managed user (not a group) or an application present in Microsoft Entra ID.

If the new owner is a Microsoft Entra user, it cannot exist as a user in the database where the new owner will become the new database owner (dbo). The Microsoft Entra user must first be removed from the database before executing the ALTER AUTHORIZATION statement changing the database ownership to the new user. For more information about configuring Microsoft Entra users with SQL Database, see Configure Microsoft Entra authentication.

Requirements for the person executing the ALTER AUTHORIZATION statement: You must connect to the target database to change the owner of that database.

The following types of accounts can change the owner of a database.

The service-level principal login, which is the SQL administrator provisioned when the logical server in Azure was created.
The Microsoft Entra administrator for the logical server..
The current owner of the database.

The following table summarizes the requirements:

Executor	Target	Result
SQL Server Authentication login	SQL Server Authentication login	Success
SQL Server Authentication login	Microsoft Entra user	Fail
Microsoft Entra user	SQL Server Authentication login	Success
Microsoft Entra user	Microsoft Entra user	Success

To verify a Microsoft Entra owner of the database, execute the following Transact-SQL command in a user database (in this example testdb).

SELECT CAST(owner_sid as uniqueidentifier) AS Owner_SID
FROM sys.databases
WHERE name = 'testdb';

The output will be a GUID (such as XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX) which corresponds to the object ID of the Microsoft Entra user or service principal assigned as the database owner. You can verify this by checking the user's object ID in Microsoft Entra ID. When a SQL Server authentication login user is the database owner, execute the following statement in the master database to verify the database owner:

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;

Best practice

Instead of using Microsoft Entra users as individual owners of the database, use a Microsoft Entra group as a member of the db_owner fixed database role. The following steps show how to configure a disabled login as the database owner, and make a Microsoft Entra group (mydbogroup) a member of the db_owner role.

Login to SQL Server as Microsoft Entra admin, and change the owner of the database to a disabled SQL Server authentication login. For example, from the user database execute:
```
ALTER AUTHORIZATION ON database::testdb TO DisabledLogin;
```
Create a Microsoft Entra group that should own the database and add it as a user to the user database. For example:
```
CREATE USER [mydbogroup] FROM EXTERNAL PROVIDER;
```
In the user database add the user representing the Microsoft Entra group, to the db_owner fixed database role. For example:
```
ALTER ROLE db_owner ADD MEMBER mydbogroup;
```

Now the mydbogroup members can centrally manage the database as members of the db_owner role.

When members of this group are removed from the Microsoft Entra group, they automatically lose the dbo permissions for this database.
Similarly if new members are added to mydbogroup Microsoft Entra group, they automatically gain the dbo access for this database.

To check if a specific user has the effective dbo permission, have the user execute the following statement:

SELECT IS_MEMBER ('db_owner');

A return value of 1 indicates the user is a member of the role.

Permissions

Requires TAKE OWNERSHIP permission on the entity. If the new owner is not the user that is executing this statement, also requires either, 1) IMPERSONATE permission on the new owner if it is a user or login; or 2) if the new owner is a role, membership in the role, or ALTER permission on the role; or 3) if the new owner is an application role, ALTER permission on the application role.

Examples

A. Transfer ownership of a table

The following example transfers ownership of table Sprockets to user MichikoOsada. The table is located inside schema Parts.

ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada;
GO

The query could also look like the following:

ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;
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. For example:

ALTER AUTHORIZATION ON Sprockets TO MichikoOsada;
ALTER AUTHORIZATION ON OBJECT::Sprockets TO MichikoOsada;

B. Transfer ownership of a view to the schema owner

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

ALTER AUTHORIZATION ON OBJECT::Production.ProductionView06 TO SCHEMA OWNER;
GO

C. Transfer ownership of a schema to a user

The following example transfers ownership of the schema SeattleProduction11 to user SandraAlayo.

ALTER AUTHORIZATION ON SCHEMA::SeattleProduction11 TO SandraAlayo;
GO

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

Applies to: SQL Server 2008 (10.0.x) and later.

ALTER AUTHORIZATION ON ENDPOINT::CantabSalesServer1 TO JaePak;
GO

E. Changing the owner of a table

Each of the following examples changes the owner of the Sprockets table in the Parts database to the database user MichikoOsada.

ALTER AUTHORIZATION ON Sprockets TO MichikoOsada;
ALTER AUTHORIZATION ON dbo.Sprockets TO MichikoOsada;
ALTER AUTHORIZATION ON OBJECT::Sprockets TO MichikoOsada;
ALTER AUTHORIZATION ON OBJECT::dbo.Sprockets TO MichikoOsada;

F. Changing the owner of a database

Applies to: SQL Server 2008 (10.0.x) and later, Analytics Platform System (PDW), SQL Database.

The following example change the owner of the Parts database to the login MichikoOsada.

ALTER AUTHORIZATION ON DATABASE::Parts TO MichikoOsada;

G. Changing the owner of a database to a Microsoft Entra user

In the following example, a Microsoft Entra administrator for SQL Server in an organization with a custom Microsoft Entra domain named cqclinic.onmicrosoft.com, can change the current ownership of a database targetDB and make an existing Microsoft Entra user richel@cqclinic.onmicorsoft.com the new database owner using the following command:

ALTER AUTHORIZATION ON database::targetDB TO [rachel@cqclinic.onmicrosoft.com];

Partajați prin

ALTER AUTHORIZATION (Transact-SQL)

Syntax

Arguments

Remarks

Special Cases and Conditions

ALTER AUTHORIZATION for databases

For SQL Server

For Azure SQL Database

Best practice

Permissions

Examples

A. Transfer ownership of a table

B. Transfer ownership of a view to the schema owner

C. Transfer ownership of a schema to a user

E. Changing the owner of a table

F. Changing the owner of a database

G. Changing the owner of a database to a Microsoft Entra user

See Also

Feedback

Resurse suplimentare

Partajați prin

ALTER AUTHORIZATION (Transact-SQL)

Syntax

Arguments

Remarks

Special Cases and Conditions

ALTER AUTHORIZATION for databases

For SQL Server

For Azure SQL Database

Best practice

Permissions

Examples

A. Transfer ownership of a table

B. Transfer ownership of a view to the schema owner

C. Transfer ownership of a schema to a user

D. Transfer ownership of an endpoint to a SQL Server login

E. Changing the owner of a table

F. Changing the owner of a database

G. Changing the owner of a database to a Microsoft Entra user

See Also

Feedback

Resurse suplimentare