TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

ALTER AUTHORIZATION (Transact-SQL)

 

Updated: May 23, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Changes the ownership of a securable.

Topic link icon Transact-SQL Syntax Conventions

-- Syntax for SQL Server  
  
ALTER AUTHORIZATION    
   ON [ <class_type>:: ] entity_name    
   TO { principal_name | SCHEMA OWNER }    
[;]    
    
<class_type> ::=    
    {    
        OBJECT | ASSEMBLY | ASYMMETRIC KEY | 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 Azure SQL Data Warehouse  
  
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    
}    

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

OBJECTAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.
ASSEMBLYAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database.
ASYMMETRIC KEYAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database.
CERTIFICATEAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database.
CONTRACTAPPLIES TO: SQL Server 2008 through SQL Server 2016.
DATABASEAPPLIES TO: SQL Server 2008 through SQL Server 2016. For more information,see ALTER AUTHORIZATION FOR databases section below.
ENDPOINTAPPLIES TO: SQL Server 2008 through SQL Server 2016.
FULLTEXT CATALOGAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database.
FULLTEXT STOPLISTAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database.
MESSAGE TYPEAPPLIES TO: SQL Server 2008 through SQL Server 2016.
REMOTE SERVICE BINDINGAPPLIES TO: SQL Server 2008 through SQL Server 2016.
ROLEAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database.
ROUTEAPPLIES TO: SQL Server 2008 through SQL Server 2016.
SCHEMAAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.
SEARCH PROPERTY LISTAPPLIES TO: SQL Server 2012 through SQL Server 2016, Azure SQL Database.
SERVER ROLEAPPLIES TO: SQL Server 2008 through SQL Server 2016.
SERVICEAPPLIES TO: SQL Server 2008 through SQL Server 2016.
SYMMETRIC KEYAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database.
TYPEAPPLIES TO: SQL Server 2008 through SQL Server 2016, Azure SQL Database.
XML SCHEMA COLLECTIONAPPLIES TO: SQL Server 2008 through SQL Server 2016, 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.

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.

System_CAPS_ICON_important.jpg Important


Beginning with SQL Server 2005, 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.

System_CAPS_ICON_caution.jpg Caution


In SQL Server 2005, 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. For more information about catalog views, see Catalog Views (Transact-SQL).

Also, note the following:

System_CAPS_ICON_important.jpg Important


The only reliable way to find the owner of a 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.

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

ClassCondition
OBJECTCannot change ownership of triggers, constraints, rules, defaults, statistics, system objects, queues, indexed views, or tables with indexed views.
SCHEMAWhen 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.
TYPECannot change ownership of a TYPE that belongs to sys or information_schema.
CONTRACT, MESSAGE TYPE, or SERVICECannot change ownership of system entities.
SYMMETRIC KEYCannot change ownership of global temporary keys.
CERTIFICATE or ASYMMETRIC KEYCannot transfer ownership of these entities to a role or group.
ENDPOINTThe principal must be a login.

APPLIES TO: SQL Server.

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.

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.

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    

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

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 through SQL Server 2016.
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 through SQL Server 2016, Parallel Data Warehouse.

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

ALTER AUTHORIZATION ON DATABASE::Parts TO MichikoOsada;    

OBJECTPROPERTY (Transact-SQL)
TYPEPROPERTY (Transact-SQL)
EVENTDATA (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft