USE Secure
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 CONVERT(varchar(30),SUSER_NAME()) as SUSER_NAME, CONVERT(varchar(30),USER_NAME()) as USER_NAME;

-- Set the execution context to login1. 
EXECUTE AS LOGIN = 'login1';

--Verify the execution context is now login1.
SELECT CONVERT(varchar(30),SUSER_NAME()) as SUSER_NAME, CONVERT(varchar(30),USER_NAME()) as USER_NAME;

--Login1 sets the execution context to login2.
EXECUTE AS USER = 'user2';

--Display current execution context.
SELECT CONVERT(varchar(30),SUSER_NAME()) as SUSER_NAME, CONVERT(varchar(30),USER_NAME()) as 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 CONVERT(varchar(30),SUSER_NAME()) as SUSER_NAME, CONVERT(varchar(30),USER_NAME()) as USER_NAME;

REVERT;

--Display current execution context.
SELECT CONVERT(varchar(30),SUSER_NAME()) as SUSER_NAME, CONVERT(varchar(30),USER_NAME()) as USER_NAME;

-- Remove users and logins

DROP USER user1
DROP USER user2
DROP LOGIN login1
DROP LOGIN login2
GO
