EXECUTE AS 子句 (Transact-SQL)EXECUTE AS Clause (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server 中,可以定义以下用户定义模块的执行上下文:函数(内联表值函数除外)、过程、队列和触发器。In SQL ServerSQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

通过指定执行模块的上下文,可以控制 数据库引擎Database Engine使用哪一个用户帐户来验证对模块引用的对象的权限。By specifying the context in which the module is executed, you can control which user account the 数据库引擎Database Engine uses to validate permissions on objects that are referenced by the module. 这有助于人们更灵活、有力地管理用户定义的模块及其所引用对象所形成的对象链中的权限。This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. 必须而且只需授予用户对模块自身的权限,而无需授予用户对被引用对象的显式权限。Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. 只有运行模块的用户必须对模块访问的对象拥有权限。Only the user that the module is running as must have permissions on the objects accessed by the module.

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

语法Syntax

-- SQL Server Syntax  
Functions (except inline table-valued functions), Stored Procedures, and DML Triggers  
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }   
  
DDL Triggers with Database Scope  
{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }   
  
DDL Triggers with Server Scope and logon triggers  
{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }   
  
Queues  
{ EXEC | EXECUTE } AS { SELF | OWNER | 'user_name' }   
  
-- Windows Azure SQL Database Syntax  
Functions (except inline table-valued functions), Stored Procedures, and DML Triggers  
  
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }   
  
DDL Triggers with Database Scope  
  
{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }  
  

参数Arguments

CALLERCALLER
指定模块内的语句在模块调用方的上下文中执行。Specifies the statements inside the module are executed in the context of the caller of the module. 执行模块的用户不仅必须对模块本身拥有适当的权限,还要对模块引用的任何数据库对象拥有适当权限。The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module.

CALLER 是除队列外的所有模块的默认值,与 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 行为相同。CALLER is the default for all modules except queues, and is the same as SQL Server 2005 (9.x)SQL Server 2005 (9.x) behavior.

CALLER 不能在 CREATE QUEUE 或 ALTER QUEUE 语句中指定。CALLER cannot be specified in a CREATE QUEUE or ALTER QUEUE statement.

SELFSELF
EXECUTE AS SELF 与 EXECUTE AS user_name 等价,其中指定用户是创建或更改模块的用户。EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or altering the module. 创建或更改模块的用户的实际用户 ID 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。The actual user ID of the person creating or modifying the modules is stored in the execute_as_principal_id column in the sys.sql_modules or sys.service_queues catalog view.

SELF 是队列的默认值。SELF is the default for queues.

备注

若要在 sys.service_queues 目录视图中更改 execute_as_principal_id 的用户 ID,必须在 ALTER QUEUE 语句中显式指定 EXECUTE AS 设置。To change the user ID of the execute_as_principal_id in the sys.service_queues catalog view, you must explicitly specify the EXECUTE AS setting in the ALTER QUEUE statement.

OWNEROWNER
指定模块内的语句在模块的当前所有者上下文中执行。Specifies the statements inside the module executes in the context of the current owner of the module. 如果模块没有指定的所有者,则使用模块架构的所有者。If the module does not have a specified owner, the owner of the schema of the module is used. 不能为 DDL 或登录触发器指定 OWNER。OWNER cannot be specified for DDL or logon triggers.

重要

OWNER 必须映射到单一实例帐户,并且不能是角色或组。OWNER must map to a singleton account and cannot be a role or group.

' user_name '' user_name '
指定模块内的语句在 user_name 指定的用户的上下文中执行。Specifies the statements inside the module execute in the context of the user specified in user_name. 将根据 user_name 来验证对模块内任意对象的权限。Permissions for any objects within the module are verified against user_name. 不能为具有服务器作用域的 DDL 触发器或登录触发器指定 user_name。user_name cannot be specified for DDL triggers with server scope or logon triggers. 请改用 login_name。Use login_name instead.

user_name 必须存在于当前数据库中,并且必须是单一实例帐户。user_name must exist in the current database and must be a singleton account. user_name 不能为组、角色、证书、密钥或内置帐户,如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。user_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

执行上下文的用户 ID 存储在元数据中,可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。The user ID of the execution context is stored in metadata and can be viewed in the execute_as_principal_id column in the sys.sql_modules or sys.assembly_modules catalog view.

' login_name '' login_name '
指定模块内的语句在 login_name 指定的 SQL ServerSQL Server 登录的上下文中执行。Specifies the statements inside the module execute in the context of the SQL ServerSQL Server login specified in login_name. 将根据 login_name 来验证对模块内任意对象的权限。Permissions for any objects within the module are verified against login_name. 只能为具有服务器作用域的 DDL 触发器或登录触发器指定 login_name。login_name can be specified only for DDL triggers with server scope or logon triggers.

login_name 不能为组、角色、证书、密钥或内置帐户,如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。login_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

RemarksRemarks

数据库引擎Database Engine对模块所引用对象的权限进行评估的方式取决于调用对象和被引用对象之间存在的所有权链。How the 数据库引擎Database Engine evaluates permissions on the objects that are referenced in the module depends on the ownership chain that exists between calling objects and referenced objects. SQL ServerSQL Server 的早期版本中,所有权链是可以避免授权调用用户访问所有被引用对象权限的唯一方式。In earlier versions of SQL ServerSQL Server, ownership chaining was the only method available to avoid having to grant the calling user access to all referenced objects.

所有权链具有以下限制:Ownership chaining has the following limitations:

  • 仅适用于 DML 语句:SELECT、INSERT、UPDATE 和 DELETE。Applies only to DML statements: SELECT, INSERT, UPDATE, and DELETE.

  • 调用和被调用对象的所有者必须相同。The owners of the calling and the called objects must be the same.

  • 不适用于模块内的动态查询。Does not apply to dynamic queries inside the module.

不论模块中指定的执行上下文如何,以下操作始终适用:Regardless of the execution context that is specified in the module, the following actions always apply:

  • 执行模块时,数据库引擎Database Engine首先验证执行模块的用户是否拥有对模块的 EXECUTE 权限。When the module is executed, the 数据库引擎Database Engine first verifies that the user executing the module has EXECUTE permission on the module.

  • 所有权链规则继续应用。Ownership chaining rules continue to apply. 这意味着如果调用和被调用对象的所有者相同,则不检查对基础对象的权限。This means if the owners of the calling and called objects are the same, no permissions are checked on the underlying objects.

当某用户要执行已指定在 CALLER 之外的上下文中执行的模块时,系统将检查该用户是否有执行该模块的权限,但对该模块访问的对象的权限检查,则是针对 EXECUTE AS 子句中指定的用户帐户执行。When a user executes a module that has been specified to run in a context other than CALLER, the user's permission to execute the module is checked, but additional permissions checks on objects that are accessed by the module are performed against the user account specified in the EXECUTE AS clause. 实际上,执行模块的用户将扮演指定的用户。The user executing the module is, in effect, impersonating the specified user.

在模块的 EXECUTE AS 子句中指定的上下文,仅在模块执行期间有效。The context specified in the EXECUTE AS clause of the module is valid only for the duration of the module execution. 模块执行完毕后,上下文反转为调用方。Context reverts to the caller when the module execution is completed.

指定用户名或登录名Specifying a User or Login Name

修改模块以便在其他上下文中执行前,不能删除模块的 EXECUTE AS 子句中指定的数据库用户或服务器登录。A database user or server login specified in the EXECUTE AS clause of a module cannot be dropped until the module has been modified to execute under another context.

在 EXECUTE AS 子句中指定的用户或登录名必须分别是 sys.database_principals 或 sys.server_principals 中的主体,否则创建或更改模块的操作将失败。The user or login name specified in EXECUTE AS clause must exist as a principal in sys.database_principals or sys.server_principals, respectively, or else the create or alter module operation fails. 此外,创建或更改模块的用户必须拥有对主体的 IMPERSONATE 权限。Additionally, the user that creates or alters the module must have IMPERSONATE permissions on the principal.

如果用户拥有通过 Windows 组成员身份隐式访问数据库或 SQL ServerSQL Server 实例的权限,并且满足以下要求之一,则创建模块时,也将隐式创建在 EXECUTE AS 子句中指定的用户:If the user has implicit access to the database or instance of SQL ServerSQL Server through a Windows group membership, the user specified in the EXECUTE AS clause is implicitly created when the module is created when one of the following requirements exist:

  • 指定的用户或登录名是 sysadmin 固定服务器角色的成员。The specified user or login is a member of the sysadmin fixed server role.

  • 创建模块的用户拥有创建主体的权限。The user that is creating the module has permission to create principals.

如果这些要求都不满足,则创建模块的操作将失败。When neither of these requirements are met, the create module operation fails.

重要

如果 SQL ServerSQL Server (MSSQLSERVER) 服务是使用本地帐户(本地服务或本地用户帐户)运行的,它将无权获取 EXECUTE AS 子句中指定的 Windows 域帐户的组成员身份。If the SQL ServerSQL Server (MSSQLSERVER) service is running as a local account (local service or local user account), it will not have privileges to obtain the group memberships of a Windows domain account that is specified in the EXECUTE AS clause. 这将导致模块的执行失败。This will cause the execution of the module to fail.

例如,假设条件如下:For example, assume the following conditions:

  • CompanyDomain\SQLUsers 组拥有 Sales 数据库的访问权限。CompanyDomain\SQLUsers group has access to the Sales database.

  • CompanyDomain\SqlUser1 是 SQLUsers 的成员,因此它拥有 Sales 数据库的访问权限。CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has access to the Sales database.

  • 创建或更改模块的用户拥有创建主体的权限。The user that is creating or altering the module has permissions to create principals.

当运行以下 CREATE PROCEDURE 语句时,将隐式创建 CompanyDomain\SqlUser1 作为 Sales 数据库中的数据库主体。When the following CREATE PROCEDURE statement is run, the CompanyDomain\SqlUser1 is implicitly created as a database principal in the Sales database.

USE Sales;  
GO  
CREATE PROCEDURE dbo.usp_Demo  
WITH EXECUTE AS 'CompanyDomain\SqlUser1'  
AS  
SELECT user_name();  
GO  

使用 EXECUTE AS CALLER 独立语句Using EXECUTE AS CALLER Stand-Alone Statement

使用模块内的 EXECUTE AS CALLER 独立语句将执行上下文设置为模块调用方。Use the EXECUTE AS CALLER stand-alone statement inside a module to set the execution context to the caller of the module.

假定 SqlUser2 调用以下存储过程。Assume the following stored procedure is called by SqlUser2.

CREATE PROCEDURE dbo.usp_Demo  
WITH EXECUTE AS 'SqlUser1'  
AS  
SELECT user_name(); -- Shows execution context is set to SqlUser1.  
EXECUTE AS CALLER;  
SELECT user_name(); -- Shows execution context is set to SqlUser2, the caller of the module.  
REVERT;  
SELECT user_name(); -- Shows execution context is set to SqlUser1.  
GO  

使用 EXECUTE AS 定义自定义权限集Using EXECUTE AS to Define Custom Permission Sets

定义自定义权限集时,指定模块的执行上下文非常有用。Specifying an execution context for a module can be very useful when you want to define custom permission sets. 例如,某些操作(如 TRUNCATE TABLE)没有可授予的权限。For example, some actions, such as TRUNCATE TABLE, do not have grantable permissions. 通过在模块内包含 TRUNCATE TABLE 语句并指定模块作为拥有更改该表权限的用户来执行,可以将截断表的权限扩展到被授予对该模块拥有 EXECUTE 权限的用户。By incorporating the TRUNCATE TABLE statement within a module and specifying that module execute as a user who has permissions to alter the table, you can extend the permissions to truncate the table to the user to whom you grant EXECUTE permissions on the module.

若要查看具有指定执行上下文的模块的定义,请使用 sys.sql_modules (Transact-SQL) 目录视图。To view the definition of the module with the specified execution context, use the sys.sql_modules (Transact-SQL) catalog view.

最佳实践Best Practice

指定拥有执行模块中定义的操作所需的最低权限的登录或用户。Specify a login or user that has the least privileges required to perform the operations defined in the module. 例如,如果不需要相应的权限,则不要指定数据库所有者帐户。For example, do not specify a database owner account unless those permissions are required.

PermissionsPermissions

若要执行使用 EXECUTE AS 指定的模块,调用方必须拥有对模块的 EXECUTE 权限。To execute a module specified with EXECUTE AS, the caller must have EXECUTE permissions on the module.

若要执行使用 EXECUTE AS 指定的 CLR 模块(该模块将访问其他数据库或服务器资源),目标数据库或服务器必须信任从中派生模块的数据库(源数据库)的验证器。To execute a CLR module specified with EXECUTE AS that accesses resources in another database or server, the target database or server must trust the authenticator of the database from which the module originates (the source database).

若要在创建或修改模块时指定 EXECUTE AS 子句,必须对指定的主体拥有 IMPERSONATE 权限以及创建该模块的权限。To specify the EXECUTE AS clause when you create or modify a module, you must have IMPERSONATE permissions on the specified principal and also permissions to create the module. 可以始终扮演自身。You can always impersonate yourself. 如果未指定执行上下文或指定了 EXECUTE AS CALLER,则无需 IMPERSONATE 权限。When no execution context is specified or EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.

若要指定通过 Windows 组成员身份能够隐式访问数据库的 login_name 或 user_name,则必须拥有对数据库的 CONTROL 权限。To specify a login_name or user_name that has implicit access to the database through a Windows group membership, you must have CONTROL permissions on the database.

示例Examples

以下示例在 AdventureWorks2012AdventureWorks2012 数据库中创建一个存储过程,并将执行上下文分配给 OWNERThe following example creates a stored procedure in the AdventureWorks2012AdventureWorks2012 database and assigns the execution context to OWNER.

CREATE PROCEDURE HumanResources.uspEmployeesInDepartment   
@DeptValue int  
WITH EXECUTE AS OWNER  
AS  
    SET NOCOUNT ON;  
    SELECT e.BusinessEntityID, c.LastName, c.FirstName, e.JobTitle  
    FROM Person.Person AS c   
    INNER JOIN HumanResources.Employee AS e  
        ON c.BusinessEntityID = e.BusinessEntityID  
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh  
        ON e.BusinessEntityID = edh.BusinessEntityID  
    WHERE edh.DepartmentID = @DeptValue  
    ORDER BY c.LastName, c.FirstName;  
GO  
  
-- Execute the stored procedure by specifying department 5.  
EXECUTE HumanResources.uspEmployeesInDepartment 5;  
GO  
  

另请参阅See Also

sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.service_queues (Transact-SQL) sys.service_queues (Transact-SQL)
REVERT (Transact-SQL) REVERT (Transact-SQL)
EXECUTE AS (Transact-SQL)EXECUTE AS (Transact-SQL)