SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

What is impersonation for?

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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...