Testing Permissions with EXECUTE AS

When you’re developing a new database solution you’ll inevitably want to apply some security to that data. If you have complex security requirements with users being members of multiple groups or implementing multiple roles within the database it can become tricky to ensure that everything is working exactly as you like.

It’s certainly a hassle to have multiple sessions logged into the same environment with different accounts just to check the permissions are working as expected. So what if we could do it within a single query tab? – this is where EXECUTE AS can come in handy.

Setting up

We’ll set up this test by creating a simple table and putting a few values into it. We’ll also create a dummy user for our testing later too:

CREATE TABLE dbo.PermissionTest (
	ID INT PRIMARY KEY
);

INSERT INTO dbo.PermissionTest
VALUES (1), (2), (3);

CREATE USER Dummy WITHOUT LOGIN;

With that in place we’re able to run a simple query to check the number of records in the table:

SELECT COUNT(1) [Count]
FROM PermissionTest;

As we’ve just created this database we’ll have ownership on it so we’ve got no issues running a simple count on the table.

Running as another user

Now that we’ve got the table set up and our Dummy user created we want to use the EXECUTE AS statement to allow us to impersonate another user in the database and execute any future statements under the context of that user. Let’s see what happens if we try to run the same query again but this time as our new user which hasn’t been assigned any permissions:

EXECUTE AS USER = 'Dummy';

SELECT COUNT(1) [Count]
FROM PermissionTest;

The SELECT permission was denied on the object ‘PermissionTest’, database ‘Blog’, schema ‘dbo’.

As we haven’t added assigned the user any specific roles or permissions we’ll see the SELECT permission is denied just like if we’d actually logged into the server with the account and ran the query in its own session.

Just to note that you can replace the USER argument with a LOGIN argument and the impersonation will take place at the server rather than database level.

Reverting the context

We don’t only want to be able to switch into the context of this user we want to also be able to get out too. Fortunately that’s just as easy with a single statement:

REVERT;

It’s worth mentioning at this point that you can have multiple impersonations in the same session, so we can enter another context with the same Dummy account whilst we’re in the current one. This means we’d need to REVERT the appropriate number of times to escape. For example…

EXECUTE AS USER = 'Dummy';
SELECT USER_NAME();
EXECUTE AS USER = 'Dummy';
SELECT USER_NAME();
REVERT;
SELECT USER_NAME();
REVERT;
SELECT USER_NAME();

Dummy

Dummy

Dummy

dbo

Wrap up

This is of course a very short and trivial example to show how this feature can be used. It’s not often I’ve needed to try and work through some obscure security issues with a database but I’ve found this a really helpful tool when those moments arise.

It can also be used for testing any new features which you might have added such as dynamic data masking (which we’ll come back to soon).

If you’d like to read more about the EXECUTE AS statement you can find further details in the online documentation.