EXECUTE AS (Transact-SQL)

Sets the execution context of a session.

By default, a session starts when a user logs in and ends when the user logs off. All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement. In essence, the user or login account is impersonated for the duration of the session or module execution, or the context switch is explicitly reverted. For more information about execution context, see Understanding Execution Context. For more information about context switching, see Understanding Context Switching.

Transact-SQL Syntax Conventions

Syntax

{ EXEC | EXECUTE ] AS <context_specification>
[;]

<context_specification>::=
{ LOGIN | USER } = 'name'
    [ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ] 
| CALLER

Arguments

• LOGIN
  Specifies the execution context to be impersonated is a login. The scope of impersonation is at the server level.

• USER
  Specifies the context to be impersonated is a user in the current database. The scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the server-level permissions of that user.

  Important

  While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers. To extend the scope of the context switch outside the current database, see Extending Database Impersonation by Using EXECUTE AS.

• 'name'
  Is a valid user or login name. name must be a member of the sysadmin fixed server role, or exist as a principal in sys.database_principals or sys.server_principals, respectively.

  name can be specified as a local variable.

  name must be a singleton account, and cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

  For more information, see Specifying a User or Login Name later in this topic.

• NO REVERT
  Specifies that the context switch cannot be reverted back to the previous context.

  For more information about reverting to the previous context, see REVERT (Transact-SQL).

• COOKIE INTO **@**varbinary_variable
  Specifies the execution context can only be reverted back to the previous context if the calling REVERT WITH COOKIE statement contains the correct **@**varbinary_variable value. The SQL Server 2005 Database Engine passes the cookie to **@**varbinary_variable.

  **@**varbinary_variable is varbinary(100).

• CALLER
  When used inside a module, specifies the statements inside the module are executed in the context of the caller of the module.

  When used outside a module, the statement has no action.

Remarks

The change in execution context remains in effect until one of the following occurs:

• Another EXECUTE AS statement is run.
• A REVERT statement is run.
• The session is dropped.

You can create an execution context stack by calling the EXECUTE AS statement multiple times across multiple principals. When called, the REVERT statement switches the context to the login or user in the next level up in the context stack. For a demonstration of this behavior, see Example A.

Specifying a User or Login Name

The user or login name specified in EXECUTE AS <context_specification> must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the EXECUTE AS statement fails. Additionally, IMPERSONATE permissions must be granted on the principal. Unless the caller is the database owner, or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership. For example, assume the following conditions:

• CompanyDomain\SQLUsers group has access to the Sales database.
• CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has implicit access to the Sales database.

Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE AS USER = 'CompanyDomain\SqlUser1' fails because CompanyDomain\SqlUser1 does not exist as a principal in the database.

These steps are not required for NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

Best Practice

Specify a login or user that has the least privileges required to perform the operations in the session. For example, do not specify a login name with server-level permissions, if only database-level permissions are required; or do not specify a database owner account unless those permissions are required.

Using WITH NO REVERT

When the EXECUTE AS statement includes the optional WITH NO REVERT clause, the execution context of a session cannot be reset using REVERT or by executing another EXECUTE AS statement. The context set by the statement remains in affect until the session is dropped.

When the WITH NO REVERT COOKIE = @varbinary_variable clause is specified, the SQL Server Database Engine passes the cookie value to @varbinary_variable. The execution context set by that statement can only be reverted to the previous context if the calling REVERT WITH COOKIE = @varbinary_variable statement contains the same @varbinary_variable value.

This option is useful in an environment in which connection pooling is used. Connection pooling is the maintenance of a group of database connections for reuse by applications on an application server. Because the value passed to @varbinary_variable is known only to the caller of the EXECUTE AS statement, the caller can guarantee that the execution context they establish cannot be changed by anyone else.

Determining the Original Login

Use the ORIGINAL_LOGIN function to return the name of the login that connected to the instance of SQL Server. You can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context switches.

Permissions

To specify EXECUTE AS on a login, the caller must have IMPERSONATE permissions on the specified login name. To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. When EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.

Examples

A. Using EXECUTE AS and REVERT to switch context

The following example creates a context execution stack using multiple principals. The REVERT statement is then used to reset the execution context to the previous caller. The REVERT statement is executed multiple times moving up the stack until the execution context is set to the original caller.

USE AdventureWorks;
GO
--Create two temporary principals
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';
GO
CREATE USER user1 FOR LOGIN login1;
CREATE USER user2 FOR LOGIN login2;
GO
--Give IMPERSONATE permissions on user2 to user1
--so that user1 can successfully set the execution context to user2.
GRANT IMPERSONATE ON USER:: user2 TO user1;
GO
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
-- Set the execution context to login1. 
EXECUTE AS LOGIN = 'login1';
--Verify the execution context is now login1.
SELECT SUSER_NAME(), USER_NAME();
--Login1 sets the execution context to login2.
EXECUTE AS USER = 'user2';
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
-- The execution context stack now has three principals: the originating caller, login1 and login2.
--The following REVERT statements will reset the execution context to the previous context.
REVERT;
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
REVERT;
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();

--Remove temporary principals.
DROP LOGIN login1;
DROP LOGIN login2;
DROP USER user1;
DROP USER user2;
GO

B. Using the WITH COOKIE clause

The following example sets the execution context of a session to a specified user and specifies the WITH NO REVERT COOKIE = @varbinary_variable clause. The REVERT statement must specify the value passed to the @cookie variable in the EXECUTE AS statement to successfully revert the context back to the caller. To run this example, the login1 login and user1 user created in example A must exist.

DECLARE @cookie varbinary(100);
EXECUTE AS USER = 'user1' WITH COOKIE INTO @cookie;
-- Store the cookie in a safe location in your application.
-- Verify the context switch.
SELECT SUSER_NAME(), USER_NAME();
--Display the cookie value.
SELECT @cookie;
GO
-- Use the cookie in the REVERT statement.
DECLARE @cookie varbinary(100);
-- Set the cookie value to the one from the SELECT @cookie statement.
SET @cookie = <value from the SELECT @cookie statement>;
REVERT WITH COOKIE = @cookie;
-- Verify the context switch reverted.
SELECT SUSER_NAME(), USER_NAME();
GO

See Also

Reference

REVERT (Transact-SQL)
EXECUTE AS Clause (Transact-SQL)

Other Resources

Context Switching

Help and Information

Getting SQL Server 2005 Assistance

Change History