Using impersonation to allow a task without granting the required permission.


August 1, 2016 by Kenneth Fisher

I use impersonation on a regular basis for a number of different tasks. This one is pretty basic but can make a big difference in how you handle security. Let’s use a very basic task as an example.

A developer wants to be able to truncate a table.

This isn’t an unreasonable request right? She’s writing a piece of code that loads a bunch of data into a staging table. She want’s to be able to truncate the table once the load is over. Unfortunately the permission required to do this is ALTER on the table. That’s not just going to let her truncate the table, it’s going to let her change the structure of the table. Not acceptable in a production environment. A development environment sure. Not a production one. So what do we do?

We use impersonation.

We are going to create a stored procedure to do the truncate for her. One of the options when creating a stored procedure is the EXECUTE AS clause that causes the sp to run as if another user had actually run it. We proceed one of two ways.

One option is to:

  1. Create a new user, generally SQL Server authenticated, just for this purpose.
  2. Grant the user alter permissions on the table to be truncated.
  3. Write a stored procedure that does the truncate on the table.
  4. Use the EXECUTE AS ‘UserName’ clause in the stored procedure.
  5. Grant EXECUTE permissions on the stored procedure to the user that needs to truncate the table.

Now when the stored procedure runs it will run as if it’s UserName and has UserName’s permissions. Therefore it will be able to truncate the table. UserName can also alter the table, but we aren’t sharing UserName’s password with our devs.

The other way, simpler, and in some ways more secure, is to use EXECUTE AS OWNER. As long as the owner of the schema of the stored procedure and the owner of the schema of the table are the same then no explicit permissions or special user are required. To explain that another way when we use EXECUTE AS OWNER the stored procedure runs as if it was the owner of the stored procedure. So if the stored procedure is in the dbo schema, that happens to be owned by dbo the databases owner, then the stored procedure is run as if it was being run by the database owner.

That doesn’t sound overly safe does it? It helps if you understand that the only people who can create a stored procedure that impersonates another user are the people who have the ability to impersonate that user. So in this case the stored procedure will probably be created by a DBA who is a member of db_owner or sysadmin. That DBA can control exactly what the stored procedure does, so there is no more risk than anything else a DBA can do.

So here is an example of the stored procedure we have been talking about:

CREATE PROCEDURE dbo.Truncate_UserTest 
GRANT EXECUTE ON Truncate_UserTest TO DevWhoNeedsToTruncate;

9 thoughts on “Using impersonation to allow a task without granting the required permission.

  1. Antony Clark says:

    I’ve generally created a User WITHOUT LOGIN to use as the execution context, just for this purpose.

  2. Very nice, concise article ! Thank you !!

  3. Ray Herring says:

    Nice Kenneth,

    Anthony, do you have a naming convention for your impersonation user?

  4. […] Granting the ability to perform a task without granting the permission(s): Another way to use impersonation is to use the EXECUTE AS clause in a stored procedure. Essentially the stored procedure runs with the permissions of a different user. By doing this you can give someone the ability to TRUNCATE a table without giving them ALTER on the table (the required permission). […]

  5. […] is a highly useful tool in your toolbox. With the EXECUTE AS clause you can allow your stored procedure to do things that the user doesn’t have permission […]

Leave a Reply to Antony Clark Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: