The trials and tribulations of REVERTing from impersonation

Kenneth Fisher, 2018-05-16

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads