[2k5] 한 세션에서 사용자 컨텍스트 전환(EXECUTE AS)

1. /**********************************************************************************************
   -- Title : [2k5] 한 세션에서 사용자 컨텍스트 전환(EXECUTE AS)
   -- Reference : dbRang.com
   -- Key word : execute as user revert impersonate grant create login user
   **********************************************************************************************/
   USE AdventureWorks;
   GO
   
   --Create two temporary principals
   CREATE LOGIN login1 WITH PASSWORD = 'login1';
   CREATE LOGIN login2 WITH PASSWORD = 'login2';
   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.
   --참조 : http://technet.microsoft.com/ko-kr/library/ms173848.aspx
   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
   
   /*
   -- EXECUTE AS는 프로시저에서도 사용된다.
   */