Understanding Context Switching
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. In SQL Server 2005, the execution context can be switched to another user or login by executing the EXECUTE AS statement, or specifying the EXECUTE AS clause in a module. After the context switch, SQL Server checks permissions against the login and user for that account instead of the person calling the EXECUTE AS statement or the module. The database user or SQL Server login is impersonated for the remainder of the session or module execution, or until the context switch is explicitly reverted. For more information about execution context, see Understanding Execution Context and the Security Tutorial, Ownership Chains and Context Switching.
Explicit Context Switching
The execution context of a session or module can be explicitly changed by specifying a user or login name in an EXECUTE AS statement. The impersonation remains in effect until one of the following events occurs:
- The session is dropped.
- Context is switched to another login or user.
- Context is reverted to the previous execution context.
Using EXECUTE AS to explicitly impersonate another user is similar to SETUSER in earlier versions of SQL Server. For more information, see EXECUTE AS vs. SETUSER.
Explicit Server-Level Context Switching
To switch execution context at the server level, use the EXECUTE AS LOGIN = 'login_name' statement. The login name must be visible as a principal in sys.server_principals, and the statement caller must have IMPERSONATE permission on the specified login name.
The scope of impersonation, when execution context is at the server level, is as follows:
- The login token for login_name is authenticated by the instance of SQL Server and is valid across that instance.
- Server-level permissions and role memberships of login_name are honored.
Use the REVERT statement to return to the previous context. The caller of the REVERT statement must be in the same database where the impersonation occurred.
Example
In the following example, Peter Connelly, a network administrator for Adventure Works Cycles, wants to create a SQL Server login account for a new employee, Jinghao Liuhas. Peter's SQL Server login does not have the server-level permission required to create SQL Server logins, but it does have IMPERSONATE permission on adventure-works\dan1, a SQL Server login that does have the required server-level permission. When Peter connects to SQL Server, the execution context for the session is derived from his SQL Server login. In order to create a SQL Server login, Peter temporarily assumes the execution context of adventure-works\dan1. He then creates the login. Finally, he relinquishes his assumed permissions.
-- Switch execution context to the adventure-works\dan1 login account.
EXECUTE AS LOGIN = 'adventure-works\dan1';
-- Create the new login account.
CREATE LOGIN Jinghao1 WITH PASSWORD = '3KHJ6dhx(0xVYsdf';
-- Revert to the previous execution context.
REVERT;
Explicit Database-Level Context Switching
To switch context at the database level, use the EXECUTE AS USER = 'user_name' statement. The user name must exist as a principal in sys.database_principals and the statement caller must have IMPERSONATE permissions on the specified user name.
The scope of impersonation, when execution context is at the database level, is as follows:
- The user token for user_name is authenticated by the instance of SQL Server and is valid in the current database. For information about how to extend user impersonation beyond the scope of the current database, see Extending Database Impersonation by Using EXECUTE AS.
- Database-level permissions and role memberships of user_name for the current database are honored. Server-level permissions granted explicitly to identities in the user token or through role memberships are not honored.
Use the REVERT statement to return to the previous context. The caller of the REVERT statement must be in the same database where the impersonation occurred.
Example
In the following example, François Ajenstat, a database administrator for Adventure Works Cycles, wants to run the CREATE TABLE statement against the AdventureWorksDW database, but he does not have the database-level permissions to do this. However, he does have IMPERSONATE permissions on user dan1, an account that has the required permission.
When François connects to the AdventureWorksDW database, the execution context maps to his user security token. Permissions to execute statements are checked against the primary and secondary principals in his user token. Because he does not have the permissions required to run the CREATE TABLE statement, he executes the following statements.
-- EXECUTE AS USER = 'dan1';
-- Create a table in dan1's default schema
CREATE TABLE t_NewTable( data nvarchar(100) );
go
-- Revert to the previous execution context.
REVERT
go;
Implicit Context Switching
The execution context of a module, such as a stored procedure, trigger, queue, or user-defined function, can be implicitly changed by specifying a user or login name in an EXECUTE AS clause in the module definition.
By specifying the context in which the module is executed, you can control which user account SQL Server uses to validate permissions on any objects referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions can be granted to users on the module itself, without having to grant explicit permissions on the referenced objects. Only the user that the module is impersonating needs to have permissions on the objects accessed by the module.
The level of impersonation is determined by the type of module in which the impersonation is defined.
Server-level impersonation can be defined in the following:
- DDL triggers
The scope of server-level impersonation is the same as that previously defined in "Explicit Server-Level Context Switching."
Database-level impersonation can be defined in the following:
- DML triggers
- Queues
- Stored procedures
- User-defined functions
- The scope of database-level impersonation is the same as that previously defined in "Explicit Database-Level Context Switching."
- For more information about implicit context switching, see Using EXECUTE AS in Modules.
Example
In the following example, Mary is the owner of the table MyTable. She wants user Scott to be able to truncate the table, but Scott has no direct permissions on the table. So, she creates the stored procedure dbo.usp_TruncateMyTable and grants EXECUTE permissions on the procedure to Scott. When Scott executes the stored procedure, the Database Engine verifies the permissions to truncate the table as if Mary herself were executing the stored procedure. Because she is the table owner, the statement succeeds even though Scott has no direct permissions on the table itself.
CREATE PROCEDURE dbo.usp_TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;
See Also
Other Resources
Context Switching
EXECUTE (Transact-SQL)
EXECUTE AS Clause (Transact-SQL)
EXECUTE AS (Transact-SQL)
REVERT (Transact-SQL)