SQL Server->> revert expression

Revert expressions are available from SQL Server 2005. It is used to toggle the execution context of the current procedure to return the security context before the previous EXECUTE AS statement occurred. Revert can be used in stored procedures, AD-HOC environments, and user-defined functions. Revert is required to be used in conjunction with EXECUTE AS.

Here's an example of MSDN

--Create the temporary principals.CREATELOGIN Login1 withPASSWORD= 'j345#$) THB';CREATELOGIN login2 withPASSWORD= 'uor80$23b';GOCREATE USERUser1 forLOGIN login1;CREATE USERUser2 forLOGIN login2;GO--Give impersonate permissions on User2 to User1--So this user1 can successfully set the execution context to user2.GRANTImpersonate on USER:: User2 touser1;GO--Display Current execution context.SELECT Suser_name(),user_name();--Set The execution context to login1.EXECUTE  asLOGIN= 'Login1';--Verify that the execution context was 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 would 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.DROPLOGIN login1;DROPLOGIN login2;DROP USERuser1;DROP USERUser2;GO    

Revert also supports a with COOKIE = @varbinary_variable option. The purpose of this thing is to ensure that the context of the current session is not toggled by the next person to reuse the session, in case the connection pool is connection. This thing is like a password, you save the password, only you know the password, to decode.

--Create Temporary principalCREATELOGIN Login1 withPASSWORD= '[email protected]$ $w 0rdo1'; GO CREATE USERUser1 forLOGIN login1;GODECLARE @cookie varbinary( -);--variable to store the cookie--switch execution context, generate cookie and assign it to variableEXECUTE  as USER = 'User1'  withCookies into @cookie;Select @cookieSelect Current_UserEXECUTE  as USER = 'User2';--Use the cookie in the REVERT statement.SELECT Current_User  asUserName; DECLARE @cookie varbinary( -); --Set the cookie value to the one from the SELECT @cookie statement.SET @cookie = 0x21873959e804dd435976ea5d25b7352431a98b4f144c76f6b1502c5aa3c20f30105842eea9c361b3da03b2dbd36e0e070100; REVERT withCookies= @cookie; --Verify the context switch reverted.SELECT Current_User  asUserName; GO

Reference:

REVERT (Transact-SQL)

Switching Stored Procedure execution Context in SQL Server using the REVERT clause

SQL Server->> revert expression