PtiTom

EXECUTE AS Samples

May 23rd, 2020
92
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --Create two temporary principals  
  2. CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';  
  3. CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';  
  4. GO  
  5. CREATE USER user1 FOR LOGIN login1;  
  6. CREATE USER user2 FOR LOGIN login2;  
  7. GO  
  8. --Give IMPERSONATE permissions on user2 to user1  
  9. --so that user1 can successfully set the execution context to user2.  
  10. GRANT IMPERSONATE ON USER:: user2 TO user1;  
  11. GO  
  12. --Display current execution context.  
  13. SELECT SUSER_NAME(), USER_NAME();  
  14. -- Set the execution context to login1.  
  15. EXECUTE AS LOGIN = 'login1';  
  16. --Verify the execution context is now login1.  
  17. SELECT SUSER_NAME(), USER_NAME();  
  18. --Login1 sets the execution context to login2.  
  19. EXECUTE AS USER = 'user2';  
  20. --Display current execution context.  
  21. SELECT SUSER_NAME(), USER_NAME();  
  22. -- The execution context stack now has three principals: the originating caller, login1 and login2.  
  23. --The following REVERT statements will reset the execution context to the previous context.  
  24. REVERT;  
  25. --Display current execution context.  
  26. SELECT SUSER_NAME(), USER_NAME();  
  27. REVERT;  
  28. --Display current execution context.  
  29. SELECT SUSER_NAME(), USER_NAME();  
  30.  
  31. --Remove temporary principals.  
  32. DROP LOGIN login1;  
  33. DROP LOGIN login2;  
  34. DROP USER user1;  
  35. DROP USER user2;  
  36. GO
RAW Paste Data