What is impersonation for?


December 27, 2017 by Kenneth Fisher

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


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

2 thoughts on “What is impersonation for?

  1. Robert Eder says:

    I use impersonation any time I add a database to an availability group so that my user account is not the owner of the secondary databases. Also, I use impersonation to restore a database in preparation for log shipping so that my user account is not the owner of the database. In both these scenarios, the database owner cannot be changed.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 2,168 other followers

Follow me on Twitter

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