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?

up vote 5 down vote accepted

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.

  • I understand that we can use this to collect information, but what about the security concerns when you grant impersonation rights to user A on user B. All the activities performed by A will be logged as done by user B, isn't it? If we are comfortable with letting user A have same level of access as user B, why can't we provide access to user A directly which will help in logging appropriately ? – karun_r Jul 15 '16 at 18:26
  • @karun10 - As far as auditing is concerned the ORIGINAL_LOGIN function is what should be used to get the true identity with impersonation disregarded. – Martin Smith Jul 15 '16 at 18:28
  • Honestly I've never had a reason to grant someone impersonate rights directly. I use them as a sysadmin/dbo frequently though, and they come in handy in SPs (like I said above). You might grant someone impersonate rights so there is an extra step of work before being able to do a task. – Kenneth Fisher Jul 15 '16 at 18:38
  • You should also be aware that, since members of db_owner have impersonate, if you set trustworthy to on for a database and the owner is listed as a member of the sysadmin role, such as sa. Then anyone in the db_owner role for the database can use impersonation to elevate permissions from a member of db_owner on a single database to a member of the sysadmin role. – Michael Keleher Jul 15 '16 at 19:01
  • Yep. That's the link I put above. Generally I recommend if you are going to do that (and think about it carefully before hand) create a special database just for the purpose with SPs to do specific tasks.. Then only grant CONNECT and EXECUTE to the SPs a principal needs. – Kenneth Fisher Jul 15 '16 at 19:08

You may wish to allow a user to execute an extended stored proc, or other privileged operation, but only under a certain circumstance.

Write a stored procedure that contains EXECUTE AS that performs the privileged operation, then give the user permissions to that stored procedure. That way they can only perform that privileged operation within the context of that stored procedure, which you have written to perform a strictly limited operation.

Adding to what has already been said in the other two answers (by @KennethFisher and @REvans), the IMPERSONATE permission also allows a User who is neither in the dbo database role or sysadmin server role the ability to set the AUTHORIZATION property of an object (one that has that property, not all do) to a User other than themselves. For example:

CREATE ASSEMBLY [AnnoyTsqlPurists]
  AUTHORIZATION [SomeoneElse]
  FROM 0x4D59.........................;

To clarify / amend what the others have said regarding limited elevation of permissions via EXECUTE AS, it should be noted that EXECUTE AS isn't required to do such things, at least not anymore. While using EXECUTE AS is the easier path, it giving a User or Login the ability to act as another User or Login doesn't control the context of when EXECUTE AS can be issued. Meaning, take the example of User_A being granted IMPERSONATE User_B for the purpose of being able to do something like TRUNCATE TABLE, and then granted EXECUTE on a stored procedure that has both the EXECUTE AS User = 'User_B'; and TRUNCATE TABLE statements in it. That does work. However, it does not prevent User_A from running EXECUTE AS User = 'User_B'; whenever they want, even outside of that specific Stored Procedure. And if you need to give someone a more general permission, such as VIEW SERVER STATE, then they can impersonate that "elevated" user to make use of that elevated permission outside of the most likely intended much narrower desired application of that permission, such as getting data from one particular DMV, not all DMVs that require that permission.

Fortunately there is a mechanism that allows for being very granular and explicit when wanting / needing to allow non-privileged Users to do higher level "stuff": module signing. Using this approach you set up a Login and/or User (Asymmetric Key-based or Certificate-based) that holds the desired permissions(s), but this Login and/or User can't be impersonated as they can't log on. Then you associate the Login and/or User to one or more modules (Stored Procedure, Function (excluding Inline TVF), Trigger, or Assembly) via ADD SIGNATURE. Finally, grant EXECUTE on the module(s) to the non-privileged Users. Now the non-privileged Users have no access to the source of the elevated permissions.

For more details, please see my following two answers (and their links to even more answers), also here on DBA.SE:

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.