The trials and tribulations of REVERTing from impersonation

1

May 16, 2018 by Kenneth Fisher

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.

One thought on “The trials and tribulations of REVERTing from impersonation

  1. […] Kenneth Fisher shows a couple common issues when executing as another user or login: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,182 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: