//
//
The switching of execution context of the module is
valid only for the duration of the module execution or as long as no REVERT
statement is used or switching the context to another login/user or session
is disconnected.
Example: –
In this example, we create a stored procedure which will be called by a
database user to log an error in a log table and will be executed in the
execution context of the caller. This stored procedure will first check if the
log table already exists, if not it will impersonate the database owner (which
has privileges to create a table) to create the log table and grant permission
to INSERT and SELECT from the table to the database user and finally revert the
execution context back to caller.
|
USE GO — Create two temporary principals for demonstration. CREATELOGIN CREATELOGIN GO — Create two users and map them with created logins CREATEUSER CREATEUSER GO — Give IMPERSONATE permissions on DBOwner to DBUser — so that DBUser can successfully set the execution context to GRANT 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 — already exists or else create the table first and logs the CREATEPROCEDURE dbo.LogError(@ErrorMessage VARCHAR(200)) WITHEXECUTEASCALLER AS BEGIN — Display the execution context (Login and User) of the SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName]; IFOBJECT_ID(‘GlobalLog’)ISNULL BEGIN — Switching execution context to DBOwner, a more EXECUTEASUSER=’DBOwner’; — Display the execution context (Login and User) of the SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName]; CREATETABLE GlobalLog ( [ID] INTPRIMARYKEYIDENTITY, ErrorMessage VARCHAR(200) ); — Grant user permission to insert and select from the GRANTSELECT, INSERTON GlobalLog TO DBUser; — Reverting back the execution context to the original REVERT; — Display the execution context (Login and User) of the SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName]; END — Display the execution context (Login and User) of the SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName]; — Insert the log record INSERTINTO GlobalLog( END GO — Grant user permission to execute the stored procedure GRANTEXECUTEON dbo.LogError TO DBUser |