SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The trials and tribulations of REVERTing from impersonation

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...