SQL Server Permissions – Use of IMPERSONATE Permissions

impersonationpermissionssql server

During some reading, got to know about the Impersonate permissions. From what I've read, it is more like creating a copy of the user with all the permission levels under a different name. I understand that this can be used for executing any queries under a different login but ultimately what purpose does it serve ?

Why was this feature introduced?

Best Answer

Personally I use impersonation for three major categories of tasks.

Testing If I need to test what access someone has I can impersonate them, try out the task, and see if it works. This is particularly useful when I've granted permissions but the user is still telling me they can't perform a given task.

Collecting information There are a number of system views/functions that give you information about the connecting principals permissions (even some AD information). As an example if I impersonate a database principal (user) and query sys.user_token I can get a list of all AD groups they are a member of and which ones give them access to the current database.

Granting access to a task without granting the permission A specific example here is the ability to truncate a table. In order to truncate a table you have to have the ALTER permission on the table. I want to let some truncate that table but I don't want to risk them making changes to it.

  1. Create a database principal (user) that has alter on the table
  2. Create a stored procedure that does the TRUNCATE and uses EXECUTE AS to cause the SP to run as the user I created.
  3. Grant execute permissions execute to the stored procedure.

You can even use this technique to grant sysadmin level permissions although it does have it's own difficulties and risks.

Edit:

I can see two possible reasons why you might grant someone impersonate rights.

Separate application permissions from direct access permissions

ApplicationA requires that user Joe have access to read from any table. But as part of Joe's responsibilities he also needs to update a status table in case something needs to be re-written. By granting update permissions to user UpdatePerms and granting Joe impersonate access to it he can log into SSMS, impersonate that user and update the table. This means he has no update access through the application but can still perform this occasional task.

Require extra thought/action before performing a task

Similar to above. Joe needs to be able to delete rows from a table, but you don't want him to do it by accident (or at least make it harder). By requiring he impersonate another user before performing the delete he at least has to think about it a bit harder making it less likely to happen by mistake.

Note: I've never had to do either of these in production. It just seems like logical possibilities.

Related Question