What is impersonation for?

Kenneth Fisher, 2017-12-27

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads