SQL Server ->> Revert表达式
时间：2021-07-01 10:21:17
帮助过：71人阅读
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
REVERT还支持一个WITH COOKIE = @varbinary_variable选项。这个东西的作用主要是为了保证在启动连接池（connection pool）的情况下，当前会话的上下文不被下一个重用会话的人切换。这个东西就像一个密码一样的东西，你保存了密码，就只有你自己知道密码，才能去解码。
--Create temporary principal
CREATE LOGIN login1 WITH PASSWORD = ‘P@$$w0rdO1‘;
GO
CREATE USER user1 FOR LOGIN login1;
GO
DECLARE @cookie varbinary(100); --variable to store the cookie
--switch execution context, generate cookie and assign it to variable
EXECUTE AS USER = ‘user1‘ WITH COOKIE INTO @cookie;
select @cookie
select CURRENT_USER
EXECUTE AS USER = ‘user2‘;
-- Use the cookie in the REVERT statement.
SELECT CURRENT_USER AS UserName;
DECLARE @cookie varbinary(100);
-- Set the cookie value to the one from the SELECT @cookie statement.
SET @cookie = 0x21873959E804DD435976EA5D25B7352431A98B4F144C76F6B1502C5AA3C20F30105842EEA9C361B3DA03B2DBD36E0E070100;
REVERT WITH COOKIE = @cookie;
-- Verify the context switch reverted.
SELECT CURRENT_USER AS UserName;
GO
参考：
REVERT (Transact-SQL)
Switching Stored Procedure Execution Context in SQL Server using the REVERT clause
SQL Server ->> Revert表达式
标签：
