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 
WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE UserTest;
GO
GRANT EXECUTE ON Truncate_UserTest TO DevWhoNeedsToTruncate;

Filed under: Impersonation, Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tagged: Impersonation, microsoft sql server, security, T-SQL