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?
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.
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.
EXECUTE ASto cause the SP to run as the user I created.
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.
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:
can someone help me with the answer for this question
In SQL Server, User1 is granted IMPERSONATE on User2. User2 owns the emp table and has prevented any other user from accessing the emp table. User1 attempts to update the table emp owned by User2. Based on this, which of the following statements are correct?
A. Auditing will show that User1 updated table emp.
B. User1 will be able to update table emp.
C. User1 cannot update table emp.
D. User1 will have to use User2's password to update the emp table.
E. User2 will be disabled while User1 has IMPERSONATE on User2's account.