I was doing some research on impersonation the other day, and among other things, I ran across a forum question Use of IMPERSONATE permissions in SQL Server? I started reading the answer, and it seemed like a pretty good answer, well laid out, decent formatting etc. I was honestly shocked when I realized I was the one who posted the answer. It seemed a lot better than something I would have written. Long story short (too late), I thought this subject would make a good post.

So why do we use impersonation?

Administrative tasks

Testing: An administrator impersonates a user to make sure that their existing permissions are sufficient for a task. They might do this because the user is having a problem and they want to eliminate permissions as an issue, or they may have just granted permissions and want to make sure everything works the way they expect.

Information: Certain, really useful, system views/functions return information about your account/security related to your account. By impersonating someone else’s account you can use these system views/functions to collect information about someone else. For example sys.login_token returns (among other things) a list of all AD groups that you belong to, along with which ones are principals on this instance.

User tasks

Seperation of permissions: By granting required permissions to an alternate id it requires an additional step and additional thought to perform the task. For example, an employee normally only reads from a table, but occasionally needs to write to it. The data in that table is highly sensitive so you don’t want to grant the employee write permissions normally. By granting write permissions to an alternate user the employee has to stop, perform the impersonation, then do the write. Hopefully forcing the employee to take a second thought about what they are about to do.

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).

Considerations

Impersonation is a very useful tool, but it can have some serious security implications. If you grant someone permission to impersonate a user that is a member of the db_owner role then you have just granted that person db_owner permissions. Because of this, any security rules you have in place (requiring documentation before granting db_owner for example) should be used anytime you are granting impersonation rights. (Based on the rights of the account being impersonated.)