Testing as another user…without their password

As a DBA or SQL Developer, you’ve surely encountered a scenario where something worked for you, but not for another user. DBAs usually are part of the sysadmin role, or have a collection of powerful permissions. If I run code as myself, I will never have permissions problems because my account is sysadmin. If I want to ensure permissions are correct, I need to run the code as the less-privileged account.

Can you try now?

Can you try now?

Can you try now?

Can you try now?

I honestly don’t think there is a more frustrating experience than getting into the feedback loop of making a permission change, and asking the user if it works, and when it doesn’t work, you try another change, and ask them to try again, and when it doesn’t work, you try another change, and ask them to try again, and when it doesn’t work, you try another change, and ask them to try again, and when it doesn’t work, you try another change, and ask them to try again…

Yeah, that wasn’t fun to read, imagine being part of it as it happens.

It’s a much better customer service experience to just test the permission change yourself, by running the code as the other login/user account.

Don’t you need the password for that?

I often see DBAs grabbing passwords out of a password vault so that they can execute code as that generic/application user. I’ve even occasionally heard DBAs ask a developer/user for username & password so they could log in as that account to test permissions.

Don’t do that

There’s no need for the password. You just need to impersonate the other user. If you are a sysadmin or have CONTROL SERVER, then you can impersonate any other login. You can also GRANT IMPERSONATE permission to a login.

Warning

Note that the IMPERSONATE permission creates a vector by which you could inadvertently allow a user to escalate their own permissions to a more powerful account–you should be very careful about granting this permission. I generally recommend granting it only in non-production environments, and only allowing very specific use cases, such as allowing a developer to impersonate the application login for the application they actively develop. Never allow a non-sysadmin to impersonate a security admin or sysadmin.

Using impersonation

Let’s take a look at impersonation in a real life scenario: A user comes to me, and says when they execute the dbo.GetNewWidgets stored procedure, they get this error:

The user has EXECUTE permission on dbo.GetNewWidgets, so ownership chaining should mean that the user doesn’t need explicit permissions on the underlying tables. I want to try running it myself so that I can troubleshoot permissions & make sure it’s fixed.

All I need to know is what code they are running, and what login they are using. In this case, the user is logged in using Windows Authentication with their user name AM2\TheUser and they are executing the procedure with no parameters. Logged in as my (sysadmin) account, I just need to run this, and I can reproduce the issue:

USE Widgets;
GO
EXECUTE AS LOGIN='AM2\TheUser';
EXEC dbo.GetNewWidgets;
REVERT;

After bashing my head against my desk a few times, I realize that I’m calling a procedure in the Widgets database, and the error message is referencing a table in the Farkles database. Because this call crosses database boundaries, and we’re not using cross-database ownership chaining, I do need to explicitly grant read permissions on the database. Eventually, I am able to run the above code to confirm that it now runs as AM2\TheUser without error, and can report back with confidence that the issue is fixed.

Note that there are two parts of the impersonation: EXECUTE AS LOGIN='...'; and REVERT;. The scope of the EXECUTE AS will be similar to that of a #temp table: The security context change will survive for your entire session, unless you explicitly REVERT back (similar to the way a #temp table survives until you drop it). If you use EXECUTE AS within a stored procedure (or trigger), the security context change will affect all called code within the scope of that stored procedure–however, when the stored procedure exits, the security context change will automatically revert.

It’s also important to know that you can “nest” EXECUTE AS calls. If you run EXECUTE AS five times from the same session, then you will have to REVERT five times (or just terminate your session completely).

When nested EXECUTE AS makes sense

Let’s look at another example of using impersonation. Let’s say I’ve given the website development team permission to impersonate the WebAppUser SQL login:

USE master; --Must be run in master
GO
GRANT IMPERSONATE ON LOGIN::WebAppUser TO [AM2\WebDevTeam];

Then Jane User on that team reports some problem when she is running code as WebAppUser, and I want to reproduce their exact workflow to ensure that I am able to resolve the issue for them:

EXECUTE AS LOGIN = 'AM2\JaneUser'; 
--I am now impersonating Jane
EXECUTE AS LOGIN = 'WebAppUser'; 
--I am now impersonating Jane impersonating WebAppUser
EXEC dbo.GetNewWidgets;
REVERT;
--I am now back to impersonating only Jane
REVERT;
--I am now back to being myself

That gets very meta–I can impersonate another user, so that I can test their permissions when they impersonate yet a different user!

Another practical use for impersonation

When you restore or create a database, the user performing the restore/create is automatically made the owner of the database. Normally, you probably use sp_changedbowner (even though that’s deprecated, and you should be using ALTER AUTHORIZATION instead. That normally works great, except…

When you’re restoring a database WITH NORECOVERY to be an AG secondary, or a log shipping secondary, you can’t use ALTER AUTHORIZATION nor sp_changedbowner because the DB is read-only and SQL Server won’t let you execute those changes. As a result, I often see AG & log shipping secondaries with inconsistent (or downright random) owners.

Instead, when you’re doing your restore, simply use EXECUTE AS LOGIN = 'sa' (or whatever login “should” own the database), and you can keep your read-only secondary servers with nice tidy DB owners, too!

EXECUTE AS LOGIN = 'sa'; 

RESTORE DATABASE [ReallyImportantDatabase] FROM DISK = '\\Backups\FileShare\ReallyImportantDatabase\ReallyImportantDatabase.bak' WITH NORECOVERY;

REVERT;