Changing the Security Context with EXECUTE AS and REVERT

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:

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:

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:

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:

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:

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

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.