Testing as another user…without their password
Tags: sql, sqlserver, security
Published: 2020-01-01 15:13:32

---
title: Testing as another user…without their password
published: true
date: 2019-12-18 16:50:35 UTC
tags: sql, sqlserver, security
canonical_url: https://am2.co/2019/12/testing-as-another-user-without-their-password/
---

[![](http://am2.co/wp-content/uploads/greg-ortega-1LCzr14Ah5U-unsplash-1024x684.jpg)](https://www.youtube.com/watch?v=6AXd5tcyneE)<figcaption id="caption-attachment-2381">This guy doesn’t have Elvis’s password and is still able to impersonate him.</figcaption>

  
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](https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15) 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](https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-principal-permissions-transact-sql?view=sql-server-ver15#examples).

#### 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:

> 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](https://www.mssqltips.com/sqlservertip/1778/ownership-chaining-in-sql-server-security-feature-or-security-risk/) 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:

```sql
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](https://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/), 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:

```sql
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:

```sql
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](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changedbowner-transact-sql), and you should be using `ALTER AUTHORIZATION` [instead](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql). 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!

```sql
EXECUTE AS LOGIN = 'sa'; 

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

REVERT;
```

The post [Testing as another user…without their password](https://am2.co/2019/12/testing-as-another-user-without-their-password/) appeared first on [Andy M Mallon - AM²](https://am2.co).