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.