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:

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.

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.