EXECUTE AS USER vs EXECUTE AS LOGIN

Leave a comment

July 26, 2022 by Kenneth Fisher

I use impersonation a lot. It’s a really easy way to check if someone has the permissions they are supposed to. That said, a co-worker recently had an interesting question. They were testing permissions on a synonym.

EXECUTE AS User='Domain/NetworkName';
SELECT Col1, Col2 FROM SynonymName;
-- SynonymName == OtherDB.dbo.TableName

Msg 916, Level 14, State 1, Line 3
The server principal “Domain/NetworkName” is not able to access the database “OtherDB” under the current security context.

Domain/NetworkName has access to SynonymName, access to OtherDB, and access to TableName. So why are we getting an error? And it’s not really what you would expect for an access denied error either.

The problem is that they were impersonating a database level principal (a user). That means you have access to their permissions within that database.

Notice that you can go from LoginA to either DB1 or DB2, but not directly from DB1 to DB2 or back. If you are impersonating UserA you only have access to DB1. Same with UserB and DB2. However, if you are impersonating LoginA then you can access both DB1 and DB2 since LoginA maps to both UserA and UserB. So if for whatever reason you need to get to multiple databases while impersonating a different Id make sure you are using EXECUTE LOGIN. If you only need access to a single database then you can really use either. Unless of course you want it to fail when it unexpectedly tries to access another database. And yes, I’ve used EXECUTE USER for that specific reason before.

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 )

Twitter picture

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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

Join 3,702 other followers

Follow me on Twitter

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