What do you mean by Execution Context & Context Switching? Part-2

In first part we discussed, what is Execution Context, different types of Tokens available and what all information goes into a token?

We can explicitly change the execution context of the SQL Server session by attaching the EXECUTE AS Statement; the execution context of SP (stored Procedure), UDF (User Defined Function) or trigger can also be implicitly changed by attaching EXECUTE AS clause. After switching the execution context SQL Server will check permissions against the new login\user account mentioned against the EXECUTE AS clause\statement. Impersonation will remain in effect until one of the following events occurs:

a. The session is dropped.

b. Context is switched to another login or user.

c. Context is reverted to the previous execution context.

There are two levels at which you can explicitly Switch the execution context:

a. Server Level

b. Database Level

 

Server Level

If you want to switch the execution context (or we can say if you want to impersonate) you can use EXECUTE As LOGIN = <Login Name> clause. The login name mentioned in the clause must be visible to caller when quering sys.server_principals and the caller should have impersonate permission on the mentioned login. (How to Check & Grant the impersonate Permissions for Login )

Scope of impersonation at server level is as follows:

1. Login Name is authenticated by the SQL Server Instance and is valid across the instance.

2. Both Server level and inherited permissions (from Role Membership) for the login are honored.

If you want to stop the impersonation and return to the original user security context you can use REVERT keyword. Note: While executing this statement “REVERT” the caller should be in the same DB where the impersonation was initiated.

Let’s see this with the help of an example.

In this example I am connected with a login (named Sarabpreet) the login has DBCreator Role, now I wrote couple of queries, first I used the fn_my_Permissions function to check what all permissions current login has, and then I am explicitly switching the context to Sarab_SecurityAdmin login (using EXECUTE AS statement) which has SecurityAdmin Server Role assigned. Once the context is changed I’ve again used the same fn_my_Permissions function to check what all permissions it displays. The test was successful, the function returned the permissions equivalent to DBCreator role the first time and post the execution context switch it started displaying the permissions of SecurityAdmin role.

Post the successful testing I’ve used the REVERT statement to switch back the execution context.

 

Database Level

If you want to switch the execution context (or we can say if you want to impersonate) you can use EXECUTE As USER = <User Name> statement. The user name mentioned in the clause must be visible to caller when quering sys.database_principals and the caller should have impersonate permission on the mentioned User. (How to Check & Grant the impersonate Permissions for User)

Scope of impersonation at Database level is as follows:

1. User Token is authenticated by the SQL Server Instance and is valid across the Database. You can extend it beyond the current DB <Wait for the next blog.>

2. Both Database level and inherited permissions (from Role Membership) for the User are honored. This will not honor the Server level permissions (explicitly or role based inherited)

In this example I am connected with a user (named Sarabpreet) the User has DB_DataReader Role, now I wrote couple of queries, first I used the fn_my_Permissions function to check what all permissions current User has, and then I am explicitly switching the context to Sarab_WriterRole User (using EXECUTE AS statement) which has DB_DataReader & DB_DataWriter Database Roles assigned. Once the context is changed I’ve again used the same fn_my_Permissions function to check what all permissions it displays. The test was successful; the function returned the permissions equivalent to DB_DataReader role the first time and posts the execution context switch it started displaying the permissions of DB_DataReader & DB_DataWriter role.

Post the successful testing I’ve used the REVERT statement to switch back the execution context.

This actually works.

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLDB