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.

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.

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.

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

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