Understanding Execution Context
Execution context is determined by the user or login connected to the session, or executing (calling) a module. It establishes the identity against which permissions to execute statements or perform actions are checked. Execution context is represented by a pair of security tokens: a login token and a user token. The tokens identify the primary and secondary principals against which permissions are checked and the source used to authenticate the token. 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 account has access.
User and Login Security Tokens
A security token for a user or login contains the following:
- One server or database principal as the primary identity
- One or more principals as secondary identities
- Zero or more authenticators
- The privileges and permissions of the primary and secondary identities
Principals are the individuals, groups, and processes that can request SQL Server resources. Principals are categorized by their scope of influence: Windows level, SQL Server level, or database level. For more information, see Principals.
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. For more information about authenticators see Extending Database Impersonation by Using EXECUTE AS. For more information about certificates and asymmetric keys, see Encryption Hierarchy.
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. The primary identity is the login itself. The secondary identity includes permissions inherited from rules and groups.
A user token is valid only for a specific database. It contains the primary and secondary identities against which database-level permissions are checked. The primary identity is the database user itself. The secondary identity includes permissions inherited from database roles. User tokens do not contain server-role memberships and do not honor the server-level permissions granted to the identities in the token including those that are granted to the server-level public role.
If a SQL Server login or user account is explicitly created, the login or user ID created for that account is used as the primary identity in the login or user token. When a principal has implicit access to an instance of SQL Server, or access to a database through CONTROL SERVER permissions, the primary identity in the login token is the default public role. The primary identity in the user token is public.
Important
Members of the sysadmin fixed server role always have dbo as the primary identity of their user token.
Login Token Example
Mary has a SQL Server login that is mapped to her Windows account MyDomain\Mary. To view information about the login token created for her, Mary runs this statement:
SELECT principal_id, sid, name, type, usage FROM sys.login_token;
GO
The result set might look similar to this:
principal_id sid name type usage
------------ ----------- ------------- -------------- -------------
261 0x583EA MyDomain\Mary WINDOWS LOGIN GRANT OR DENY
2 0x02 public SERVER ROLE GRANT OR DENY
(2 row(s) affected)
The result set shows that the Windows account for Mary is the primary identity for her login token. The principal_id created when her login account was created is used as the primary principal_id in the login token. The public role is listed as a secondary identity, because Mary is a member of that default role. If Mary were a member of other server-level roles, they would also be listed as secondary identities. At the time the login was created, her Windows account was validated by the instance of SQL Server. Therefore, when Mary logs into the instance of SQL Server, the instance is the authenticator of her login token. Because the instance of SQL Server is the authenticator of Mary's login token, an authenticator, such as a principal, certificate, or asymmetric key, is not returned in the query.
User Token Example
Mary has one user token for each database to which she has access. In this first example Mary is connected to the master database. To view information about the user token created for her in the master database, Mary runs this statement:
SELECT principal_id, sid, name, type, usage FROM sys.user_token;
GO
The result set might look similar to this:
principal_id sid name type usage
------------ ----------- ------------- -------------- -------------
2 NULL guest SQL USER GRANT OR DENY
0 NULL public ROLE GRANT OR DENY
(2 row(s) affected)
The result set shows that Mary is not an explicit user in the master database, but instead has access through the guest account. The primary identity for her user token is the guest user. The public role is listed as a secondary identity, because guest is a member of that default role. The user token for Mary in the master database contains all the database-level privileges and permissions for the guest user and the public role.
In the following example, an explicit user account for Mary has been created in the Sales database. Additionally, she has been added to the db_ddladmin fixed database role for that database. With Sales as the current database, Mary runs SELECT * FROM sys.user_token again.
The result set might look similar to this:
principal_id sid name type usage
------------ ----------- ------------- -------------- -------------
5 0x36CC4BBD1 Mary SQL USER GRANT OR DENY
0 NULL public ROLE GRANT OR DENY
16387 NULL db_ddladmin ROLE GRANT OR DENY
This result set reflects the user token created for Mary in the Sales database. Because Mary was explicitly added as a user to the Sales database, she is listed as the primary identity. The two roles she is a member of are listed as secondary identities. The authenticator of Mary's user token is the instance of SQL Server.
Switching Execution Context
In SQL Server 2005, the execution context of a session can be explicitly changed by specifying a user or login name in an EXECUTE AS statement. The execution context of a module, such as a stored procedure, trigger, or user-defined function, can be implicitly changed by specifying a user or login name in an EXECUTE AS clause in the module definition. When the execution context is switched to another user or login, SQL Server checks permissions against the login and user tokens for that account. In essence, that account is impersonated for the duration of the session, or module execution. For more information, see Understanding Context Switching and Ownership Chains and Context Switching.
See Also
Concepts
Other Resources
Context Switching
EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL)
EXECUTE AS Clause (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)