REVERT (Transact-SQL)REVERT (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

将执行上下文切换回最后一个 EXECUTE AS 语句的调用方。Switches the execution context back to the caller of the last EXECUTE AS statement.

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

语法Syntax

  
REVERT  
    [ WITH COOKIE = @varbinary_variable ]  

参数Arguments

WITH COOKIE = @varbinary_variableWITH COOKIE = @varbinary_variable
指定在相应的 EXECUTE AS 独立语句中创建的 cookie。Specifies the cookie that was created in a corresponding EXECUTE AS stand-alone statement. @varbinary_variable 是 varbinary(100)。@varbinary_variable is varbinary(100).

RemarksRemarks

可以在诸如存储过程或用户定义函数这样的模块中指定 REVERT,也可将它指定为独立语句。REVERT can be specified within a module such as a stored procedure or user-defined function, or as a stand-alone statement. 在模块内部指定时,REVERT 只适用于在模块中定义的 EXECUTE AS 语句。When specified inside a module, REVERT is applicable only to EXECUTE AS statements defined in the module. 例如,以下存储过程发出后跟 EXECUTE AS 语句的 REVERT 语句。For example, the following stored procedure issues an EXECUTE AS statement followed by a REVERT statement.

CREATE PROCEDURE dbo.usp_myproc   
  WITH EXECUTE AS CALLER  
AS   
    SELECT SUSER_NAME(), USER_NAME();  
    EXECUTE AS USER = 'guest';  
    SELECT SUSER_NAME(), USER_NAME();  
    REVERT;  
    SELECT SUSER_NAME(), USER_NAME();  
GO  

假定在运行存储过程的会话中,会话的执行上下文显式更改为 login1,如下例所示。Assume that in the session in which the stored procedure is run, the execution context of the session is explicitly changed to login1, as shown in the following example.

  -- Sets the execution context of the session to 'login1'.  
EXECUTE AS LOGIN = 'login1';  
GO  
EXECUTE dbo.usp_myproc;   

usp_myproc 内部定义的 REVERT 语句会切换在模块内部设置的执行上下文,但不会影响在模块外部设置的执行上下文。The REVERT statement that is defined inside usp_myproc switches the execution context set inside the module, but does not affect the execution context set outside the module. 就是说,会话的执行上下文将仍然设置为 login1That is, the execution context for the session remains set to login1.

指定为独立语句时,REVERT 将应用于在批或会话中定义的 EXECUTE AS 语句。When specified as a standalone statement, REVERT applies to EXECUTE AS statements defined within a batch or session. 如果相应的 EXECUTE AS 语句包含 WITH NO REVERT 子句,则 REVERT 无效。REVERT has no effect if the corresponding EXECUTE AS statement contains the WITH NO REVERT clause. 在这种情况下,执行上下文将保持有效状态,直到删除会话。In this case, the execution context remains in effect until the session is dropped.

用于设置会话执行上下文的 EXECUTE AS 语句可以包含可选子句 WITH NO REVERT COOKIE = @varbinary_variable。The EXECUTE AS statement that is used to set the execution context of a session can include the optional clause WITH NO REVERT COOKIE = @varbinary_variable. 运行此语句时,数据库引擎Database Engine 将 cookie 传递给 @varbinary_variable。When this statement is run, the 数据库引擎Database Engine passes the cookie 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 correct @varbinary_variable value.

此机制在使用连接池的环境中是有用的。This mechanism 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 across multiple end users. 由于只有 EXECUTE AS 语句的调用方(在这里,是应用程序)才知道传递到 @varbinary_variable 的值,因此调用方可以保证它们所建立的执行上下文无法被调用该应用程序的最终用户更改。Because the value passed to @varbinary_variable is known only to the caller of the EXECUTE AS statement (in this case, the application), the caller can guarantee that the execution context they establish cannot be changed by the end user that invokes the application. 恢复执行上下文之后,应用程序可以将上下文切换到另一个主体。After the execution context is reverted, the application can switch context to another principal.

权限Permissions

不需要任何权限。No permissions are required.

示例Examples

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

以下示例通过使用多个主体创建上下文执行堆栈。The following example creates a context execution stack by 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 that 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 the current execution context.  
SELECT SUSER_NAME(), USER_NAME();  
REVERT;  
-- Display the current execution context.  
SELECT SUSER_NAME(), USER_NAME();  
  
-- Remove the 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(100);  
EXECUTE AS USER = 'user1' WITH COOKIE INTO @cookie;  
-- Store the cookie somewhere safe 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(100);  
-- 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

EXECUTE AS (Transact-SQL) EXECUTE AS (Transact-SQL)
EXECUTE AS 子句 (Transact-SQL) EXECUTE AS Clause (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
SUSER_NAME (Transact-SQL) SUSER_NAME (Transact-SQL)
USER_NAME (Transact-SQL) USER_NAME (Transact-SQL)
CREATE LOGIN (Transact-SQL) CREATE LOGIN (Transact-SQL)
CREATE USER (Transact-SQL)CREATE USER (Transact-SQL)