EXECUTE AS (Transact-SQL)
Topic Status: Some information in this topic is pre-release and subject to change in future releases. Pre-release information describes new features or changes to existing features in Microsoft SQL Server 2014.
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.
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.
The stored procedure or trigger where the command was executed exits.
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.
If the user is orphaned (the associated login no longer exists), and the user was not created with WITHOUT LOGIN, EXECUTE AS will fail for the user.
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.
The EXECUTE AS statement can succeed as long as the Database Engine can resolve the name. If a domain user exists, Windows might be able to resolve the user for the Database Engine, even though the Windows user does not have access to SQL Server. This can lead to a condition where a login with no access to SQL Server appears to be logged in, though the impersonated login would only have the permissions granted to public or guest.
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.
To specify EXECUTE AS on a login, the caller must have IMPERSONATE permission on the specified login name and must not be denied the IMPERSONATE ANY LOGIN permission. 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.
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 AdventureWorks2012; 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(8000); 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(8000); -- 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