Context Switching or Database Impersonation using EXEC AS

USE AdventureWorks;

GO

-- Create two temporary principals for demonstration.

CREATELOGIN DBUserLogin WITHPASSWORD='ABCD1234!@#$';

CREATELOGIN DBOwnerLogin WITHPASSWORD='ZXCV9876(*&';

GO

-- Create two users and map them with created logins

CREATEUSER DBUser FORLOGIN DBUserLogin;

CREATEUSER DBOwner FORLOGIN DBOwnerLogin;

GO

-- Give IMPERSONATE permissions on DBOwner to DBUser

-- so that DBUser can successfully set the execution context to DBOwner.

GRANT IMPERSONATE ONUSER:: DBOwner TO DBUser;

GO

-- Add DBOwner user to the db_owner role 

EXECsp_addrolemember'db_owner','DBOwner'

GO

-- Create a procedure which logs error message into log table if the table

-- already exists or else create the table first and logs the error message

CREATEPROCEDURE dbo.LogError(@ErrorMessage VARCHAR(200))

WITHEXECUTEASCALLER

AS

BEGIN

       -- Display the execution context (Login and User) of the user executing this section of the code for demonstration 

       SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName];

       IFOBJECT_ID('GlobalLog')ISNULL

       BEGIN

              -- Switching execution context to DBOwner, a more privileged user to create the GlobalLog table

              EXECUTEASUSER='DBOwner';

              -- Display the execution context (Login and User) of the user executing this section of the code for demonstration

              SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName];

              CREATETABLE GlobalLog

              (

                     [ID]                 INTPRIMARYKEYIDENTITY,

                     ErrorMessage  VARCHAR(200)

              );

              -- Grant user permission to insert and select from the newly created GlobalLog table

              GRANTSELECT, INSERTON GlobalLog TO DBUser;

              -- Reverting back the execution context to the original caller to limit the permission

              REVERT;

              -- Display the execution context (Login and User) of the user executing this section of the code for demonstration

              SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName];

       END

       -- Display the execution context (Login and User) of the user executing this section of the code for demonstration

       SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName];

       -- Insert the log record

       INSERTINTO GlobalLog( ErrorMessage )VALUES ( @ErrorMessage )

END

GO

-- Grant user permission to execute the stored procedure 

GRANTEXECUTEON dbo.LogError TO DBUser