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.
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.
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.
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.
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.
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:
Msg 229, Level 14, State 5, Procedure dbo.GetNewWidgets, Line 3 [Batch Start Line 6]
The SELECT permission was denied on the object ‘Widgets’, database ‘Farkles’, schema ‘dbo’.
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).
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!
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;