What if EXECUTE AS statement executes multiple times

While discussing about Impersonation, a question raised; If we mistakenly execute EXECUTE AS statement twice, do we have to call REVERT twice?.

I was not sure about it since I have not done it but realized that it is required.

EXECUTE AS statement allows us to set the execution context by impersonating another login or user. This is useful when a code that requires a different permission set for running and user logged in has no permission for running it. Once this is set, either we have to call REVERT command to go back to the original context or it will be remained in effect until the session is dropped or the module (stored procedure or trigger) where the command was executed exist.

EXECUTE AS statement can be executed multiple times either using same Principal or different Principals (read more with SQL Server Security Concepts: Securables, Pricipals and Permissions). If need to switch back to original context, then REVERT must be called for each context opened.

As per the research I made, there is no way of reverting all context using a single command and bringing the original context back to the session. Therefore, if we have mistakenly execute the statement multiple times, no other option but call REVERT multiple times.

However, we can use sys.user_token for checking whether the original context is set or not. Here is a sample code.

The current context is based on my account. Let's change the context and execute the same.

EXECUTE AS USER = 'Peter'  

SELECT * FROM sys.user_token;
SELECT ORIGINAL_LOGIN();

As you see, now the execution context is set with Peter's account. We can use ORIGINAL_LOGIN function for checking the initial login. If EXECUTE AS USER = 'Peter' statement is executed again, then it creates another context. Here is the the result;

Now if REVERT is called, it does not bring the session back to original context because it reverts only the last one created.

REVERT;

SELECT * FROM sys.user_token;
SELECT ORIGINAL_LOGIN();

Therefore, REVERT has to be called again. Here is another way of checking whether the session uses the original execution context or not.

SELECT login_name, original_login_name, * 
FROM sys.dm_exec_sessions
WHERE program_name like '%Management Studio%'