Impersonation is a highly useful tool in your toolbox. With the EXECUTE AS clause you can allow your stored procedure to do things that the user doesn’t have permission to. EXECUTE AS LOGIN/USER gives you the ability to pretend you are someone else test/view their permissions. I frequently use this technique to solve permissions problems.

Trying to run a query as someone else to see if they really have access to that database

EXECUTE AS LOGIN = 'Doc';
GO
USE Test;
SELECT * FROM Table1;

Checking what Active Directory groups someone belongs to

EXECUTE AS LOGIN = 'Kenneth-Laptop\Dopey';
SELECT * FROM sys.login_token;

The problem is that sometimes I’ll forget to revert back.

EXECUTE AS LOGIN = 'Doc';

Msg 15406, Level 16, State 1, Line 8
Cannot execute as the server principal because the principal “Doc” does not exist, this type of principal cannot be impersonated, or you do not have permission.

Hey! I know I Doc exists, and I’m sysadmin. How is this not working? Simple enough, I forgot to REVERT. There are two simple options here. I either completely forgot to run it, or I was in the wrong database.

Msg 15199, Level 16, State 1, Line 7
The current security context cannot be reverted. Please switch to the original database where ‘Execute As’ was called and try it again.

As the error says, you have to be in the same database as when you ran the EXECUTE AS to begin with.

Mistakes will always happen and this one isn’t exactly a big risk. Just be aware that it happens, and if you see a funny error try running REVERT a few times. If you aren’t impersonating anyone, there is no error so no big deal :).

Oh, and if you’re getting that I’m in the wrong database error and don’t know which database to switch back to, just reconnect. To my knowledge, there is no way to know the initial database you were in, and it’s easier to just reconnect than to try every database on the instance.