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

Execution context is maintained by the login that is connected or executing the query, then it checks the permissions of the login against the currently requested query whether the login has the required permissions to perform this action or not.

In SQLServer we can easily change the execution context to any login by executing the EXECUTE AS statement\clause. Once the execution context is switched the remaining sessions (queries) will run under the execution context of another user (as if another user is executing that piece of code) until the execution context is explicitly reverted.

We can represent the Execution Context with a pair of security tokens, which are Login & User token. The tokens help in identifying the primary and secondary principals for which the permissions will be checked. A login connecting to an instance of SQL Server has one login token and one (or more) user tokens depending on the number of databases to which the login has access.

What all information resides in a Token?

A security token whether it is Login or User contains the below mentioned information:

1. One server or database principal as the primary identity

2. One or more principals as secondary identities

3. Zero or more authenticators

4. The privileges and permissions of the primary and secondary identities

Authenticators are principals, certificates, or asymmetric keys that vouch for the authenticity of the token. Frequently, the authenticator of a token is the instance of SQL Server.

A login token is valid across the instance of SQL Server. It contains the primary and secondary identities against which server-level permissions and any database-level permissions associated with these identities are checked. A Primary Identity is a login itself whereas a Secondary Identity is referred to the permissions inherited from Roles or Groups. Because Users are created at Database level Scope a user tokens do not contain any server-role memberships or permissions.

To visualize what exactly goes in a Token, let’s execute couple of queries.

SELECT * FROM sys.login_token;

GO

SELECT * FROM Sys.User_Token;

If Sarabpreet login were a member of other server-level roles, they would also be listed as secondary identities.

Point to Note: Members of the sysadmin fixed server role always have dbo as the primary identity of their user token.

In next parts we’ll learn How to actually perform Execution Context Switching and what all options are available to do so.

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