ALTER AUTHORIZATION (Transact-SQL)ALTER AUTHORIZATION (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

更改安全对象的所有权。Changes the ownership of a securable.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法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  
  
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    
}    

备注

Azure Synapse Analytics 中的无服务器 SQL 池(预览版)不支持此语法。This syntax is not supported by serverless SQL pool (preview) in Azure Synapse Analytics.

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

<class_type>:更改其所有者的实体的安全对象类。<class_type> Is the securable class of the entity for which the owner is being changed. OBJECT 是默认值。OBJECT is the default.

Class ProductsProduct
OBJECTOBJECT 适用范围SQL Server 2008SQL Server 2008 及更高版本、Azure SQL 数据库Azure SQL DatabaseAzure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse)并行数据仓库Parallel Data WarehouseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database, Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse), 并行数据仓库Parallel Data Warehouse.
ASSEMBLYASSEMBLY 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.
ASYMMETRIC KEYASYMMETRIC KEY 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.
AVAILABILITY GROUPAVAILABILITY GROUP 适用范围:SQL Server 2012 和更高版本。APPLIES TO : SQL Server 2012 and later.
CERTIFICATECERTIFICATE 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.
CONTRACTCONTRACT 适用于SQL Server 2008SQL Server 2008 及更高版本。APPLIES TO : SQL Server 2008SQL Server 2008 and later.
DATABASEDATABASE 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database. 有关详细信息,请参阅下面的 ALTER AUTHORIZATION FOR 数据库部分。For more information,see ALTER AUTHORIZATION FOR databases section below.
ENDPOINTENDPOINT 适用于SQL Server 2008SQL Server 2008 及更高版本。APPLIES TO : SQL Server 2008SQL Server 2008 and later.
FULLTEXT CATALOGFULLTEXT CATALOG 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.
FULLTEXT STOPLISTFULLTEXT STOPLIST 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.
MESSAGE TYPEMESSAGE TYPE 适用于SQL Server 2008SQL Server 2008 及更高版本。APPLIES TO : SQL Server 2008SQL Server 2008 and later.
REMOTE SERVICE BINDINGREMOTE SERVICE BINDING 适用于SQL Server 2008SQL Server 2008 及更高版本。APPLIES TO : SQL Server 2008SQL Server 2008 and later.
ROLEROLE 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.
ROUTEROUTE 适用于SQL Server 2008SQL Server 2008 及更高版本。APPLIES TO : SQL Server 2008SQL Server 2008 and later.
SCHEMASCHEMA 适用范围SQL Server 2008SQL Server 2008 及更高版本、Azure SQL 数据库Azure SQL DatabaseAzure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse)并行数据仓库Parallel Data WarehouseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database, Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse), 并行数据仓库Parallel Data Warehouse.
SEARCH PROPERTY LISTSEARCH PROPERTY LIST 适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x)及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later, Azure SQL 数据库Azure SQL Database.
SERVER ROLESERVER ROLE 适用于SQL Server 2008SQL Server 2008 及更高版本。APPLIES TO : SQL Server 2008SQL Server 2008 and later.
SERVICESERVICE 适用于SQL Server 2008SQL Server 2008 及更高版本。APPLIES TO : SQL Server 2008SQL Server 2008 and later.
SYMMETRIC KEYSYMMETRIC KEY 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.
TYPETYPE 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.
XML SCHEMA COLLECTIONXML SCHEMA COLLECTION 适用于SQL Server 2008SQL Server 2008及更高版本、Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, Azure SQL 数据库Azure SQL Database.

entity_name entity_name
实体名。Is the name of the entity.

principal_name | SCHEMA OWNERprincipal_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). 将 SCHEMA OWNER 指定为 principal_name,指示对象必须为拥有对象架构的主体所拥有。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 可用于更改任何具有所有者的实体的所有权。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.

重要

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 开始,用户可以拥有由另一个数据库用户拥有的架构所包含的 OBJECT 或 TYPE。Beginning with SQL Server 2005 (9.x)SQL Server 2005 (9.x), a user can own an OBJECT or TYPE that is contained by a schema owned by another database user. 这是对早期版本的 SQL ServerSQL Server的行为的更改。This is a change of behavior from earlier versions of SQL ServerSQL Server. 有关详细信息,请参阅 OBJECTPROPERTY (Transact-SQL)TYPEPROPERTY (Transact-SQL)For more information, see OBJECTPROPERTY (Transact-SQL) and TYPEPROPERTY (Transact-SQL).

以下包含在架构中、类型为“object”的实体的所有权可以转移:表、视图、函数、过程、队列和同义词。Ownership of the following schema-contained entities of type "object" can be transferred: tables, views, functions, procedures, queues, and synonyms.

不能传输以下实体的所有权:链接服务器、统计信息、约束、规则、默认值、触发器、Service BrokerService Broker 队列、凭据、分区函数、分区方案、数据库主密钥、服务主密钥和事件通知。Ownership of the following entities cannot be transferred: linked servers, statistics, constraints, rules, defaults, triggers, Service BrokerService 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.

仅当转移架构包含的实体的所有权时,SCHEMA OWNER 选项才有效。The SCHEMA OWNER option is only valid when you are transferring ownership of a schema-contained entity. SCHEMA OWNER 将实体所有权转移给它所在的架构所有者。SCHEMA OWNER will transfer ownership of the entity to the owner of the schema in which it resides. 只有类 OBJECT、TYPE 或 XML SCHEMA COLLECTION 的实体是架构包含的。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.

注意

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中,架构的行为与早期版本的 SQL ServerSQL Server 中的行为不同。In SQL Server 2005 (9.x)SQL Server 2005 (9.x), the behavior of schemas changed from the behavior in earlier versions of SQL ServerSQL Server. 假设架构与数据库用户等效的代码可能不会返回正确的结果。Code that assumes that schemas are equivalent to database users may not return correct results. 旧目录视图(包括 sysobjects)不应用于曾使用下列任何 DDL 语句的数据库中: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。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. 新目录视图将采用在 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中引入的使主体和架构分离的方法。The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x). 有关目录视图的详细信息,请参阅目录视图 (Transact-SQL)For more information about catalog views, see Catalog Views (Transact-SQL).

另请注意下列事项:Also, note the following:

重要

查找对象所有者的唯一可靠的方式是查询 sys.objects 目录视图。The only reliable way to find the owner of a object is to query the sys.objects catalog view. 查找类型所有者的唯一可靠的方式是使用 TYPEPROPERTY 函数。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
OBJECTOBJECT 无法更改触发器、约束、规则、默认值、统计信息、系统对象、队列、索引视图或具有索引视图的表的所有权。Cannot change ownership of triggers, constraints, rules, defaults, statistics, system objects, queues, indexed views, or tables with indexed views.
SCHEMASCHEMA 转移所有权时,将删除没有显式所有者的架构包含对象的权限。When ownership is transferred, permissions on schema-contained objects that do not have explicit owners will be dropped. 无法更改 sys、dbo 或 information_schema 的所有者。Cannot change the owner of sys, dbo, or information_schema.
TYPETYPE 无法更改属于 sys 或 information_schema 的 TYPE 的所有权。Cannot change ownership of a TYPE that belongs to sys or information_schema.
CONTRACT、MESSAGE TYPE 或 SERVICECONTRACT, MESSAGE TYPE, or SERVICE 无法更改系统实体的所有权。Cannot change ownership of system entities.
SYMMETRIC KEYSYMMETRIC KEY 无法更改全局临时密钥的所有权。Cannot change ownership of global temporary keys.
CERTIFICATE 或 ASYMMETRIC KEYCERTIFICATE or ASYMMETRIC KEY 无法将这些实体的所有权转移给角色或组。Cannot transfer ownership of these entities to a role or group.
ENDPOINTENDPOINT 主体必须为登录名。The principal must be a login.

对数据库执行 ALTER AUTHORIZATIONALTER AUTHORIZATION for databases

适用范围SQL Server 2019 (15.x)SQL Server 2019 (15.x)Azure SQL 数据库Azure SQL DatabaseAPPLIES TO : SQL Server 2019 (15.x)SQL Server 2019 (15.x), Azure SQL 数据库Azure SQL Database.

对于 SQL Server:For SQL Server:

对新所有者的要求: Requirements for the new owner:
新所有者主体必须是以下项之一:The new owner principal must be one of the following:

  • SQL Server 身份验证登录名。A SQL Server authentication login.
  • 表示 Windows 用户(而不是组)的 Windows 身份验证登录名。A Windows authentication login representing a Windows user (not a group).
  • 表示 Windows 组的 Windows 用户,通过 Windows 身份验证登录名进行身份验证。A Windows user that authenticates through a Windows authentication login representing a Windows group.

对执行 ALTER AUTHORIZATION 语句的人员的要求:Requirements for the person executing the ALTER AUTHORIZATION statement:
如果不是 sysadmin 固定服务器角色的成员,则必须至少对数据库具有 TAKE OWNERSHIP 权限和对新所有者用户名具有 IMPERSONATE 权限。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.

对于 Azure SQL 数据库:For Azure SQL Database:

对新所有者的要求: Requirements for the new owner:
新所有者主体必须是以下项之一:The new owner principal must be one of the following:

  • SQL Server 身份验证登录名。A SQL Server authentication login.
  • 存在于 Azure AD 中的联合用户(而不是组)。A federated user (not a group) present in Azure AD.
  • 存在于 Azure AD 中的托管用户(而不是组)或应用程序。A managed user (not a group) or an application present in Azure AD.

如果新所有者是 Azure Active Directory 用户,则在新所有者将成为新 DBO 的数据库中,该新所有者不能在其中作为用户而存在。If the new owner is an Azure Active Directory user, it cannot exist as a user in the database where the new owner will become the new DBO. 执行用于将数据库所有权更改到新用户的 ALTER AUTHORIZATION 语句前,必须首先从数据库中删除此类 Azure AD 用户。Such Azure AD user must be first removed from the database before executing the ALTER AUTHORIZATION statement changing the database ownership to the new user. 有关使用 SQL 数据库配置 Azure Active Directory 用户的详细信息,请参阅使用 Azure Active Directory 身份验证连接到 SQL 数据库或 Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse)For more information about configuring an Azure Active Directory users with SQL Database, see Connecting to SQL Database or Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse) By Using Azure Active Directory Authentication.

对执行 ALTER AUTHORIZATION 语句的人员的要求: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. (创建 SQL 数据库服务器时预配的 SQL Azure 管理员。)(The SQL Azure administrator provisioned when the SQL Database server was created.)
  • Azure SQL Server 的 Azure Active Directory 管理员。The Azure Active Directory administrator for the Azure SQL Server.
  • 数据库的当前所有者。The current owner of the database.

下表概述了这些要求:The following table summarizes the requirements:

执行者Executor 目标Target 结果Result
SQL Server 身份验证登录名SQL Server Authentication login SQL Server 身份验证登录名SQL Server Authentication login SuccessSuccess
SQL Server 身份验证登录名SQL Server Authentication login Azure AD 用户Azure AD user 失败Fail
Azure AD 用户Azure AD user SQL Server 身份验证登录名SQL Server Authentication login SuccessSuccess
Azure AD 用户Azure AD user Azure AD 用户Azure AD user SuccessSuccess

若要验证数据库的 Azure AD 所有者,请在用户数据库中执行以下 Transact-SQL 命令(在此示例中为 testdb)。To verify an Azure AD 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';  

输出将为与已分配给 richel@cqclinic.onmicrosoft.com 的 Azure AD ObjectID 相对应的标识符(如 6D8B81F6-7C79-444C-8858-4AF896C03C67)The output will be an identifier (such as 6D8B81F6-7C79-444C-8858-4AF896C03C67) which corresponds to Azure AD ObjectID assigned to richel@cqclinic.onmicrosoft.com
如果 SQL Server 身份验证登录名用户是数据库所有者,请在 master 数据库中执行以下语句以验证数据库所有者: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

将 Azure AD 组用作 db_owner 固定数据库角色的成员,而不是将 Azure AD 用户用作数据库的单个所有者。Instead of using Azure AD users as individual owners of the database, use an Azure AD group as a member of the db_owner fixed database role. 下面的步骤演示如何将禁用登录名配置为数据库所有者,并将 Azure Active Directory 组 (mydbogroup) 设为 db_owner 角色的成员。The following steps, show how to configure a disabled login as the database owner, and make an Azure Active Directory group (mydbogroup) a member of the db_owner role.

  1. 以 Azure AD 管理员身份登录 SQL Server,将数据库的所有者更改为禁用的 SQL Server 身份验证登录名。Login to SQL Server as Azure AD 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;  
  1. 创建应拥有数据库的 Azure AD 组,将其作为用户添加到用户数据库。Create an Azure AD group that should own the database and add it as a user to the user database. 例如:For example:
CREATE USER [mydbogroup] FROM EXTERNAL PROVIDER;  
  1. 在用户数据库中,将表示 Azure AD 组的用户添加到 db_owner 固定数据库角色。In the user database add the user representing the Azure AD group, to the db_owner fixed database role. 例如:For example:
ALTER ROLE db_owner ADD MEMBER mydbogroup;  

现在,mydbogroup 成员可将数据库作为 db_owner 角色的成员进行集中管理。Now the mydbogroup members can centrally manage the database as members of the db_owner role.

  • 从 Azure AD 组中移除此组的成员时,他们会自动失去此数据库的 dbo 权限。When members of this group are removed from the Azure AD group, they automatically lose the dbo permissions for this database.
  • 同样,如果向 mydbogroup Azure AD 组中添加新成员,他们将自动获得此数据库的 dbo 权限。Similarly if new members are added to mydbogroup Azure AD group, they automatically gain the dbo access for this database.

若要检查特定用户是否具有有效的 dbo 权限,请让该用户执行以下语句:To check if a specific user has the effective dbo permission, have the user execute the following statement:

SELECT IS_MEMBER ('db_owner');  

返回值 1 表示该用户是角色的成员。A return value of 1 indicates the user is a member of the role.

权限Permissions

要求具有实体的 TAKE OWNERSHIP 权限。Requires TAKE OWNERSHIP permission on the entity. 如果新所有者不是执行该语句的用户,那么:1) 如果新所有者是用户或登录名,则要求具有该所有者的 IMPERSONATE 权限;2) 如果新所有者是角色,则要求具有该角色的成员身份或该角色的 ALTER 权限;3) 如果新所有者是应用程序角色,则要求具有该应用程序角色的 ALTER 权限。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.A. 转移表的所有权Transfer ownership of a table

以下示例将 Sprockets 表的所有权转移给 MichikoOsada 用户。The following example transfers ownership of table Sprockets to user MichikoOsada. 该表位于 Parts 架构内。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    

如果语句中不包含对象架构,数据库引擎Database Engine 将在用户默认架构中查找对象。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.B. 将视图的所有权转移给架构所有者Transfer ownership of a view to the schema owner

以下示例将 ProductionView06 视图的所有权转移给包含它的架构的所有者。The following example transfers ownership the view ProductionView06 to the owner of the schema that contains it. 该视图位于 Production 架构内。The view is located inside schema Production.

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

C.C. 将架构所有权转移给用户Transfer ownership of a schema to a user

以下示例将 SeattleProduction11 架构的所有权转移给 SandraAlayo 用户。The following example transfers ownership of the schema SeattleProduction11 to user SandraAlayo.

ALTER AUTHORIZATION ON SCHEMA::SeattleProduction11 TO SandraAlayo;    
GO    

D.D. 将端点的所有权转移给 SQL Server 登录名Transfer ownership of an endpoint to a SQL Server login

以下示例将 CantabSalesServer1 端点的所有权转移给 JaePakThe 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.

适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to : SQL Server 2008SQL Server 2008 and later.

ALTER AUTHORIZATION ON ENDPOINT::CantabSalesServer1 TO JaePak;    
GO    

E.E. 更改表所有者Changing the owner of a table

下面的每个示例都将 Parts 数据库中 Sprockets 表的所有者更改为数据库用户 MichikoOsadaEach 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.F. 更改数据库所有者Changing the owner of a database

适用于SQL Server 2008SQL Server 2008及更高版本、并行数据仓库Parallel Data WarehouseSQL 数据库SQL DatabaseAPPLIES TO : SQL Server 2008SQL Server 2008 and later, 并行数据仓库Parallel Data Warehouse, SQL 数据库SQL Database.

以下示例将 Parts 数据库的所有者更改为登录名 MichikoOsadaThe following example change the owner of the Parts database to the login MichikoOsada.

ALTER AUTHORIZATION ON DATABASE::Parts TO MichikoOsada;    

G.G. 将 SQL 数据库的所有者更改为 Azure AD 用户Changing the owner of a SQL Database to an Azure AD User

在下面的示例中,组织(拥有名为 cqclinic.onmicrosoft.com 的 Active Directory )中的 SQL Server 的 Azure Active Directory 管理员可以更改数据库 targetDB 的当前所有权,还将使用以下命令将 AAD 用户 richel@cqclinic.onmicorsoft.com 设为新的数据库所有者:In the following example, an Azure Active Directory administrator for SQL Server in an organization with an active directory named cqclinic.onmicrosoft.com, can change the current ownership of a database targetDB and make an AAD user richel@cqclinic.onmicorsoft.com the new database owner using the following command:

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

请注意,Azure AD 用户的用户名必须用括号括起来。Note that for Azure AD users the brackets around the user name must be used.

另请参阅See Also

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