Personally I use impersonation for three major categories of tasks.
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.
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.
- Create a database principal (user) that has alter on the table
- Create a stored procedure that does the
TRUNCATE and uses
EXECUTE AS to cause the SP to run as the user I created.
- 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.
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.