Jan 092014
 

Introduction

When testing SQL Server security, it is often necessary to login as a different user. However, repeated logins and logouts can become quite cumbersome quickly.

But there is help: Instead of establishing a new connection every time you need to test under a different login, you can instead just switch the current security context to a new login and switch it back when you are done. To execute a security context switch just use the EXECUTE AS statement. The REVERT statement lets you switch back to your original security context after you are finished.

EXECUTE AS

Let's look at an example. For that we first need two logins and two users:

CREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
CREATE LOGIN TestLogin2 WITH PASSWORD='********', CHECK_POLICY = OFF;
CREATE USER TestUser1 FOR LOGIN TestLogin1;
CREATE USER TestUser2 FOR LOGIN TestLogin2;

Now, to be able to execute the EXECUTE AS statement, you need to have impersonation permission on the account you want to switch to. So we need to grant that permission:

GRANT IMPERSONATE ON LOGIN::TestLogin2 TO TestLogin1;

As any grant or deny of server level permissions, this statement needs to be executed in master.

Now that we have the permissions out of the way, let's try the EXECUTE AS statement. Login to SQL Server using the TestLogin1 login and then execute this statement block:

SELECT 'login' AS token_type,* FROM sys.login_token AS LT
UNION ALL
SELECT 'user' AS token_type,* FROM sys.user_token AS UT;
GO
EXECUTE AS LOGIN='TestLogin2';
GO
SELECT 'login' AS token_type,* FROM sys.login_token AS LT
UNION ALL
SELECT 'user' AS token_type,* FROM sys.user_token AS UT;

I introduced the two DMVs sys.login_token and sys.user_token yesterday in The Secret of the Security Token.

When you execute the above statements you will get a result like this:

EXECUTE AS in Action

As you can see, the entire security context was switched out. Only the login TestLogin2 and the user TestUser2 shows up in the token list, after the EXECUTE AS was executed. No trace of the original login or user remains.

REVERT

To undo the context switch, you can just call REVERT anytime:

SELECT 'login' AS token_type,* FROM sys.login_token AS LT
UNION ALL
SELECT 'user' AS token_type,* FROM sys.user_token AS UT;
GO
REVERT;
GO
SELECT 'login' AS token_type,* FROM sys.login_token AS LT
UNION ALL
SELECT 'user' AS token_type,* FROM sys.user_token AS UT;

These switch the security context back using the REVERT statement. Before and after the current security tokens are displayed:

REVERT in Action

The new security context after using EXECUTE AS stays until either REVERT is used or the connection is dropped. If you however executed EXECUTE AS inside a procedure, the context is reset automatically at the end of the procedure.

Multiple EXECUTE AS executions can be nested. Each execution of the REVERT statement goes one level back up the stack. To be able to execute EXECUTE AS the current execution context needs to have IMPERSONATE permission on the target login. It does not matter if the original account had those permissions if another security context is currently active.

Summary

EXECUTE AS and REVERT allow you to switch the security context from the login you used to connect to SQL Server to any other login that you have IMPERSONATE permission on. This can simplify testing of security settings and permissions.