EXECUTE AS (Transact-SQL)EXECUTE AS (Transact-SQL)

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

设置会话的执行上下文。Sets the execution context of a session.

默认情况下,会话在用户登录时开始,在用户注销时结束。By default, a session starts when a user logs in and ends when the user logs off. 会话过程中的所有操作都受限于对该用户进行的权限检查。All operations during a session are subject to permission checks against that user. 当运行 EXECUTE AS 语句时,会话的执行上下文将切换到指定的登录名或用户名。When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. 上下文切换后,将根据登录名和用户安全令牌检查该帐户(而非调用 EXECUTE AS 语句的用户)的权限。After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement. 实际上,在会话或模块的执行期间模拟了用户或登录帐户,或显式恢复了上下文切换。In essence, the user or login account is impersonated for the duration of the session or module execution, or the context switch is explicitly reverted.

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

语法Syntax

{ EXEC | EXECUTE } AS <context_specification>  
[;]  
  
<context_specification>::=  
{ LOGIN | USER } = 'name'  
    [ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ]   
| CALLER  

参数Arguments

LoginLOGIN
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

指定要模拟的执行上下文是一个登录名。Specifies the execution context to be impersonated is a login. 模拟范围处于服务器级别。The scope of impersonation is at the server level.

备注

此选项在包含的数据库、SQL 数据库或 SQL 数据仓库中不可用。This option is not available in a contained database or SQL Database or SQL Data Warehouse.

UserUSER
指定要模拟的上下文是当前数据库中的用户。Specifies the context to be impersonated is a user in the current database. 模拟范围只限于当前数据库。The scope of impersonation is restricted to the current database. 对数据库用户的上下文切换不会继承该用户的服务器级别权限。A context switch to a database user does not inherit the server-level permissions of that user.

重要

当到数据库用户的上下文切换处于活动状态时,任何对数据库以外资源的访问尝试都将导致语句失败。While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. 这包括 USE database 语句、分布式查询以及引用其他数据库(使用由三或四部分构成的标识符)的查询。This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers.

'name' 是有效的用户或登录名。'name' Is a valid user or login name. name 必须是 sysadmin 固定服务器角色的成员,或者分别作为 sys.database_principalssys.server_principals 中的主体而存在。name must be a member of the sysadmin fixed server role, or exist as a principal in sys.database_principals or sys.server_principals, respectively.

可以将 name 指定为局部变量。name can be specified as a local variable.

name 必须是单一实例帐户,而不能是组、角色、证书、密钥或内置帐户,如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。name must be a singleton account, and cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

有关详细信息,请参阅本主题后面的指定用户名或登录名For more information, see Specifying a User or Login Name later in this topic.

NO REVERTNO REVERT
指定上下文切换不能恢复到以前的上下文。Specifies that the context switch cannot be reverted back to the previous context. NO REVERT 选项只能在临时级别使用。The NO REVERT option can only be used at the adhoc level.

有关恢复到以前上下文的详细信息,请参阅 REVERT (Transact-SQL)For more information about reverting to the previous context, see REVERT (Transact-SQL).

COOKIE INTO @varbinary_variableCOOKIE INTO @varbinary_variable
指定仅当调用 REVERT WITH COOKIE 语句包含正确的 @varbinary_variable 值时,执行上下文才能恢复回以前的上下文。Specifies the execution context can only be reverted back to the previous context if the calling REVERT WITH COOKIE statement contains the correct @varbinary_variable value. 数据库引擎Database Engine 将 cookie 传递到 @varbinary_variable。The 数据库引擎Database Engine passes the cookie to @varbinary_variable. COOKIE INTO 选项只能在临时级别使用。The COOKIE INTO option can only be used at the adhoc level.

@varbinary_variable 是 varbinary(8000)。@varbinary_variable is varbinary(8000).

备注

cookie OUTPUT 参数现记载为 varbinary(8000),这是正确的最大长度。The cookie OUTPUT parameter for is currently documented as varbinary(8000) which is the correct maximum length. 但是,当前执行返回 varbinary(100)。However the current implementation returns varbinary(100). 应用程序应保留 varbinary(8000),以便当 cookie 在将来的版本中返回大小增量时,应用程序可继续正确运行。Applications should reserve varbinary(8000) so that the application continues to operate correctly if the cookie return size increases in a future release.

CALLERCALLER
当在模块内部使用时,指定模块内部的语句在模块调用方的上下文中执行。When used inside a module, specifies the statements inside the module are executed in the context of the caller of the module. 当在模块外部使用时,语句没有任何操作。When used outside a module, the statement has no action.

备注

此选项在 SQL 数据仓库中不可用。This option is not available in SQL Data Warehouse.

RemarksRemarks

执行上下文中的更改在下列操作之一发生之前一直有效:The change in execution context remains in effect until one of the following occurs:

  • 运行另一个 EXECUTE AS 语句。Another EXECUTE AS statement is run.

  • 运行 REVERT 语句。A REVERT statement is run.

  • 删除会话。The session is dropped.

  • 命令已执行退出的存储过程或触发器。The stored procedure or trigger where the command was executed exits.

可以通过对多个主体多次调用 EXECUTE AS 语句来创建执行上下文堆栈。You can create an execution context stack by calling the EXECUTE AS statement multiple times across multiple principals. 在调用时,REVERT 语句将把上下文切换为上下文堆栈中上一级的登录帐户或用户。When called, the REVERT statement switches the context to the login or user in the next level up in the context stack. 有关此行为的示例,请参阅示例 AFor a demonstration of this behavior, see Example A.

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

EXECUTE AS <context_specification> 中指定的用户或登录名必须分别作为 sys.database_principals 或 sys.server_principals 中的主体存在,否则 EXECUTE AS 语句将失败。The user or login name specified in EXECUTE AS <context_specification> must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the EXECUTE AS statement fails. 此外,还必须为该主体授予 IMPERSONATE 权限。Additionally, IMPERSONATE permissions must be granted on the principal. 除非调用方是数据库所有者或 sysadmin 固定服务器角色的成员,否则即使用户通过 Windows 组成员身份访问数据库或 SQL ServerSQL Server 实例,主体也必须存在。Unless the caller is the database owner, or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL ServerSQL Server through a Windows group membership. 例如,假设条件如下: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 implicit access to the Sales database.

虽然 CompanyDomain\SqlUser1 可通过 SQLUsers 组的成员身份访问数据库,但语句 EXECUTE AS USER = 'CompanyDomain\SqlUser1' 失败,因为 CompanyDomain\SqlUser1 未在数据库中作为主体存在。Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE AS USER = 'CompanyDomain\SqlUser1' fails because CompanyDomain\SqlUser1 does not exist as a principal in the database.

如果用户处于孤立状态(关联登录名不再存在),并且该用户不是使用 WITHOUT LOGIN 创建的,EXECUTE AS 将针对此用户失败。If the user is orphaned (the associated login no longer exists), and the user was not created with WITHOUT LOGIN, EXECUTE AS will fail for the user.

最佳实践Best Practice

指定具有执行会话中操作所需的最低特权的登录名或用户。Specify a login or user that has the least privileges required to perform the operations in the session. 例如,如果只需要数据库级别的权限,则不要指定具有服务器级别权限的登录名;或者除非需要这些权限,否则不要指定数据库所有者帐户。For example, do not specify a login name with server-level permissions, if only database-level permissions are required; or do not specify a database owner account unless those permissions are required.

注意

只要数据库引擎Database Engine可以解析该名称,就可以成功执行 EXECUTE AS 语句。The EXECUTE AS statement can succeed as long as the 数据库引擎Database Engine can resolve the name. 如果域用户存在,Windows 可能为数据库引擎Database Engine解析用户,即使该 Windows 用户无权访问 SQL ServerSQL ServerIf a domain user exists, Windows might be able to resolve the user for the 数据库引擎Database Engine, even though the Windows user does not have access to SQL ServerSQL Server. 这可能导致这样的情况:无权访问 SQL ServerSQL Server 的登录名似乎登录了,尽管模拟的登录名仅具有给 public 或 guest 授予的权限。This can lead to a condition where a login with no access to SQL ServerSQL Server appears to be logged in, though the impersonated login would only have the permissions granted to public or guest.

使用 WITH NO REVERTUsing WITH NO REVERT

当 EXECUTE AS 语句包含可选的 WITH NO REVERT 子句时,不能通过 REVERT 语句或执行另一个 EXECUTE AS 语句来重置会话的执行上下文。When the EXECUTE AS statement includes the optional WITH NO REVERT clause, the execution context of a session cannot be reset using REVERT or by executing another EXECUTE AS statement. 由该语句设置的上下文在删除会话之前一直保持有效。The context set by the statement remains in affect until the session is dropped.

当指定 WITH NO REVERT COOKIE = @varbinary_variable 子句时,SQL Server 数据库引擎SQL Server Database Engine 将 cookie 值传递给 @varbinary_variable。When the WITH NO REVERT COOKIE = @varbinary_variable clause is specified, the SQL Server 数据库引擎SQL Server Database Engine passes the cookie value to @varbinary_variable. 只有执行调用的 REVERT WITH COOKIE = @varbinary_variable 语句包含相同的 @varbinary_variable 值时,该语句设置的执行上下文才能恢复为以前的上下文。The execution context set by that statement can only be reverted to the previous context if the calling REVERT WITH COOKIE = @varbinary_variable statement contains the same *@varbinary_variable* value.

该选项在使用连接池的环境中非常有用。This option is useful in an environment in which connection pooling is used. 连接池是指维护一组数据库连接,以使应用程序服务器上的应用程序能够重用它们。Connection pooling is the maintenance of a group of database connections for reuse by applications on an application server. 由于传递给 @varbinary_variable 的值仅对于 EXECUTE AS 语句的调用方是已知的,因此该调用方可以保证它们建立的执行上下文不能被任何其他用户更改。Because the value passed to *@varbinary_variable* is known only to the caller of the EXECUTE AS statement, the caller can guarantee that the execution context they establish cannot be changed by anyone else.

确定原始登录Determining the Original Login

使用 ORIGINAL_LOGIN 函数可以返回连接到 SQL ServerSQL Server 实例的登录名。Use the ORIGINAL_LOGIN function to return the name of the login that connected to the instance of SQL ServerSQL Server. 您可以在具有众多显式或隐式上下文切换的会话中使用该函数返回原始登录的标识。You can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context switches.

权限Permissions

若要对某登录名指定 EXECUTE AS,调用方必须具有对所指定登录名的 IMPERSONATE 权限,并且不得被拒绝 IMPERSONATE ANY LOGIN 权限。To specify EXECUTE AS on a login, the caller must have IMPERSONATE permission on the specified login name and must not be denied the IMPERSONATE ANY LOGIN permission. 若要对某数据库用户指定 EXECUTE AS,调用方必须具有对所指定用户名的 IMPERSONATE 权限。To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. 指定 EXECUTE AS CALLER 时,不需要 IMPERSONATE 权限。When EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.

示例Examples

A.A. 使用 EXECUTE AS 和 REVERT 切换上下文Using EXECUTE AS and REVERT to switch context

以下示例使用多个主体创建上下文执行堆栈。The following example creates a context execution stack using multiple principals. 然后使用 REVERT 语句将执行上下文重置为上一个调用方。The REVERT statement is then used to reset the execution context to the previous caller. 将多次执行 REVERT 语句以向上移动堆栈,直到将执行上下文设置为原始调用方为止。The REVERT statement is executed multiple times moving up the stack until the execution context is set to the original caller.

USE AdventureWorks2012;  
GO  
--Create two temporary principals  
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';  
CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';  
GO  
CREATE USER user1 FOR LOGIN login1;  
CREATE USER user2 FOR LOGIN login2;  
GO  
--Give IMPERSONATE permissions on user2 to user1  
--so that user1 can successfully set the execution context to user2.  
GRANT IMPERSONATE ON USER:: user2 TO user1;  
GO  
--Display current execution context.  
SELECT SUSER_NAME(), USER_NAME();  
-- Set the execution context to login1.   
EXECUTE AS LOGIN = 'login1';  
--Verify the execution context is now login1.  
SELECT SUSER_NAME(), USER_NAME();  
--Login1 sets the execution context to login2.  
EXECUTE AS USER = 'user2';  
--Display current execution context.  
SELECT SUSER_NAME(), USER_NAME();  
-- The execution context stack now has three principals: the originating caller, login1 and login2.  
--The following REVERT statements will reset the execution context to the previous context.  
REVERT;  
--Display current execution context.  
SELECT SUSER_NAME(), USER_NAME();  
REVERT;  
--Display current execution context.  
SELECT SUSER_NAME(), USER_NAME();  
  
--Remove temporary principals.  
DROP LOGIN login1;  
DROP LOGIN login2;  
DROP USER user1;  
DROP USER user2;  
GO  

下面的示例将会话的执行上下文设置为指定的用户,并指定 WITH NO REVERT COOKIE = @varbinary_variable 子句。The following example sets the execution context of a session to a specified user and specifies the WITH NO REVERT COOKIE = @varbinary_variable clause. REVERT 语句必须指定传递给 @cookie 语句中的 EXECUTE AS 变量的值,否则,无法成功将上下文恢复为该调用方。The REVERT statement must specify the value passed to the @cookie variable in the EXECUTE AS statement to successfully revert the context back to the caller. 若要执行此示例,必须存在示例 A 中所创建的 login1 登录名和 user1 用户。To run this example, the login1 login and user1 user created in example A must exist.

DECLARE @cookie varbinary(8000);  
EXECUTE AS USER = 'user1' WITH COOKIE INTO @cookie;  
-- Store the cookie in a safe location in your application.  
-- Verify the context switch.  
SELECT SUSER_NAME(), USER_NAME();  
--Display the cookie value.  
SELECT @cookie;  
GO  
-- Use the cookie in the REVERT statement.  
DECLARE @cookie varbinary(8000);  
-- Set the cookie value to the one from the SELECT @cookie statement.  
SET @cookie = <value from the SELECT @cookie statement>;  
REVERT WITH COOKIE = @cookie;  
-- Verify the context switch reverted.  
SELECT SUSER_NAME(), USER_NAME();  
GO  

另请参阅See Also

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