SQL Server - How to use EXECUTE AS to execute commands as another user (Impersonate login and user)

There are a few ways to do the EXECUTE AS command, where you can choose who are the users or logins to be personified by the statement. I will describe these types below in 2 scenarios where EXECUTE AS is used.

RUN AS in Ad-Hoc Queries / Instructions

• LOGIN: Allows you to impersonate a SQL Server login by getting all permissions at the instance level, inheriting permissions like CONTROL SERVER and server roles like sysadmin and securityadmin, for example.
• USER: Allows you to impersonate a SQL Server user, getting all permissions at the database level, inheriting permissions like SELECT on a table and database roles like db_datareader, for example.

Remembering that even if you are impersonating a sysadmin user through a bank that you are db_owner, for example, using EXECUTE AS USER, you will not have the sysadmin “powers” ​​of that user, as EXECUTE AS USER only inherits the database-level permissions. Since you are already db_owner, you will not inherit any new permissions that you do not already have, you can only execute commands as another user (which is still a risk). If you can use the EXECUTE AS LOGIN of a sysadmin user, then you will have all the “powers” ​​at the level of the instance that he has (that is, he can do EVERYTHING).

RUN AS on Database Objects

• CALLER: Run as CALLER is the default; If no other options are specified, the procedure will be performed in the security context of the caller.
• OWNER: Run as OWNER executes the procedure in the context of the object owner. If the object is created in a dbo-owned schema or by the database owner, the procedure runs with unlimited permissions.
• SELF: Run as SELF runs in the security context of the object creator. This is equivalent to running as specified user, where the specified user is the person who creates or changes the object.
• LOGIN / USER: As already described above, you can also use a specific user or login in the object header, allowing everyone who has EXECUTE permission on that object, such as a Stored Procedure, to execute this object with that user / login context specified automatically. .

Later on there is a specific EXECUTE AS topic in Database Objects, where I will give more details about this use.

After executing the EXECUTE AS statement to execute commands as another user, it is generally desired to return the session security context to the original user. One way to do this is to create a new session by reconnecting to the bank.

The REVERT command

The other way to do this is by using the REVERT command, which will return the security context to the previous user:

Result:

Note that the EXECUTE AS command can be nested, ie a security context tree can be created as shown below:

Result:

And now, let's undo the stack showing the nesting of this context:

Result:

EXECUTE AS… WITH COOKIES

This parameter specifies that the execution context can only be rolled back to the previous context if the REVERT WITH COOKIE invocation statement contains the correct @varbinary_variable value. This is a security measure, especially used in connection pooling, to ensure that only those who know the cookie hash can reverse the security context.

Example (Removed GO commands to prevent loss of variable information):

Result:

Now I will try to apply the REVERT command without specifying the token:

Now I will try to change the token and try to apply the REVERT command:

EXECUTE AS… WITH NO REVERT

After presenting the REVERT and REVERT WITH COOKIES commands, there is another clause that impersonate cannot be rolled back to the original user in this session. This statement is WITH NO REVERT. In this case, the only way to revert is by opening a new session.

When the WITH NO REVERT COOKIE = @varbinary_variable clause is specified, SQL Server Database Engine passes the cookie value to @varbinary_variable. The execution context defined by this statement can be reverted to the previous context only if the calling statement REVERT WITH COOKIE = @varbinary_variable has the same value @varbinary_variable.

This option is useful in an environment in which a connection pool is used. Connection pooling is the maintenance of a database connection group for application reuse on an application server. Because the value passed to @varbinary_variable is known only to the caller of the EXECUTE AS statement (in this case, the application), the caller can ensure that the established execution context cannot be changed by anyone else.

Example:

Result:

To be able to use the EXECUTE AS command on any login before SQL Server 2014, you had to have CONTROL SERVER permission or be part of the sysadmin or securityadmin server roles. From SQL Server 2014 this has become more objective with the IMPERSONATE ANY LOGIN permission, which is unique to this type of activity and does not require the person to be in the CONTROL SERVER role or a sysadmin user.

However, you who are DBA should keep in mind that this permission is EXTREMELY dangerous for an average user, since he can use EXECUTE AS to execute commands using a sysadmin login, for example, and thus do any thing in the instance.

This was one of the reasons why IMPERSONATE ANY LOGIN permission was created on SQL Server 2014. Users with CONTROL SERVER permission already had this privilege implicitly, so they could use EXECUTE AS to execute commands as another user, including sysadmin users. Starting with SQL Server 2014, the DBA can now apply a DENY IMPERSONATE ANY LOGIN command to users with CONTROL SERVER permission and prevent this from happening.

It is worth mentioning that there is also the command GRANT IMPERSONATE ON LOGIN / USER :: [user1] TO [user2], which allows freeing IMPERSONATE access to specific users. In addition, members of the database role db_owner can use EXECUTE AS on users created in these banks. Remembering that even if you are impersonating a sysadmin user through a bank that you are db_owner, for example, using EXECUTE AS USER, you will not have the sysadmin “powers” ​​of that user, as EXECUTE AS User only inherits the database-level permissions. Since you are already db_owner, you will not inherit any new permissions that you do not already have, you can only execute commands as another user (which is still a risk).

Explicit Permissions to Use IMPERSONATE

As I mentioned above, there are some conditions for using EXECUTE AS command in SQL Server.

Condition #1: IMPERSONATE ANY LOGIN

Users who have the explicit IMPERSONATE ANY LOGIN permission can execute commands like any login to the SQL Server instance. Remember that permissions are instance-level, meaning that they apply to all databases in that instance, and commands that require privileges like sysadmin and CONTROL SERVER can be used through EXECUTE AS LOGIN.

To release this permission, you must use the following command:

Condition #2: IMPERSONATE LOGIN

Users who have the IMPERSONATE LOGIN permission can execute commands as specific logins on the SQL Server instance. This command empowers a loginA to execute commands as if it were loginB and must be released for each desired login. Remember that permissions are instance-level, meaning that they apply to all databases in that instance, and commands that require privileges like sysadmin and CONTROL SERVER can be used through EXECUTE AS LOGIN.

To release this permission, you must use the following command:

Condition #3: IMPERSONATE USER

Users who have IMPERSONATE USER permission can execute commands as specific users in a database. This command empowers a user to execute commands as if it were the userB in the database that he has the permission of IMPERSONATE and must be released for each desired login. Commands that require instance level permissions (eg Shutdown, change server role members, create Linked Server, etc.) are NOT executed by IMPERSONATE USER, only commands that require database level permissions, such as INSERT, UPDATE, DELETE. , etc.

To release this permission, you must use the following command:

Implicit Permissions to Use IMPERSONATE

Now I will demonstrate that there are some implicit conditions where you can use the EXECUTE AS command on SQL Server without having an explicit permission to do so.

Condition #1: SYSADMIN

Role of SQL Server “Administrator”. Users who are in this role can do anything in the database and therefore already have the permission of IMPERSONATE LOGIN, IMPERSONATE USER and IMPERSONATE ANY LOGIN and cannot be denied privileges with DENY.

To add someone to this role, use the following command:

Condition #2: CONTROL SERVER

SQL Server “Administrator” permission. Users who have this permission can do almost anything in the database and therefore already have IMPERSONATE LOGIN, IMPERSONATE USER and IMPERSONATE ANY LOGIN permission, but, unlike role sysadmin, these users MAY have IMPERSONATE privileges denied with DENY.

To add someone to this role, use the following command:

Condition #3: SECURITYADMIN

Users who are on the server role securityadmin control the security and permissions of the SQL Server instance. In this role, these users have the IMPERSONATE ANY LOGIN permission, so they can do anything in the database as they can use the EXECUTE AS LOGIN command and execute commands as if they were a sysadmin user and for this reason, Microsoft's own documentation treats this role as equivalent to role sysadmin for security concerns. Unlike role sysadmin, these users CAN have IMPERSONATE privileges denied with DENY.

To add someone to this role, use the following command:

Condition #4: db_owner

Users who are in the database role db_owner can execute any DDL, DCL, or DML command in the database that they have this role. For this reason, these users already have the IMPERSONATE USER permission implied on all database users. Remember that these users MAY be denied IMPERSONATE privileges with DENY.

To add someone to this role, use the following command:

Condition #5: db_securityadmin

Users in the database role db_securityadmin can manage security and permissions on the database. For this reason, these users have the IMPERSONATE USER permission implicit on all database users and can execute commands as if they were one of the users in role db_owner. Remember that these users MAY be denied IMPERSONATE privileges with DENY.

To add someone to this role, use the following command:

As I mentioned earlier, to be able to use the EXECUTE AS command on any login, you must have the CONTROL SERVER permission, be part of the sysadmin or securityadmin server roles, or have the explicit IMPERSONATE ANY LOGIN privilege. If the user has the IMPERSONATE permission, they can use EXECUTE AS only on specific users who have this permission.

Speaking at the database level, where the user can run IMPERSONATE on bank users, he needs to be in the database role db_owner or has IMPERSONATE ON USER permission for the specific users he can impersonate.

Therefore, I will demonstrate below how to identify who are the users who have these permissions on the SQL Server instance.

Users with CONTROL SERVER permissions or roles sysadmin and securityadmin

To identify users with CONTROL SERVER permissions or roles sysadmin and securityadmin who can run EXECUTE AS for any login, run the script below:

Result:

Users with IMPERSONATE ANY LOGIN

To identify who are the users who have the explicit IMPERSONATE ANY LOGIN permission, and thus being able to execute commands like any user, run the script below:

Result:

Users with IMPERSONATE LOGIN

To identify who are the users who have the explicit IMPERSONATE LOGIN permission and can execute commands like some specific users, run the script below:

Result:

Users in the db_owner or db_securityadmin database roles

To identify who are the users in the database roles db_owner or db_securityadmin and can execute commands like any database user, run the script below:

Result:

Users in roles db_owner and db_securityadmin (check in all databases)

As in the previous example, to identify who are the users who are in the database roles db_owner or db_securityadmin, now parsing across all instance databases, run the script below:

Result:

Users with IMPERSONATE USER in a database

To identify who users have the explicit IMPERSONATE USER permission and can execute commands like some specific database users, run the script below:

Result:

Users with IMPERSONATE USER (check in all databases)

As in the previous example, to identify who are the users who are in the database roles sysadmin or db_securityadmin, now looking at all instance databases, run the script below:

Result:

A very common practice when using EXECUTE AS is its use in bank objects, such as Stored Procedures, to define the default permission that these objects will be executed. That is, you can create a Stored Procedure that will always be executed as the user “dirceu.resende”, who is a member of the server role sysadmin and therefore, is allowed to do anything, and anyone who has EXECUTE access to that Stored Procedure you can execute it, without having the privilege of IMPERSONATE ANY LOGIN and or have access to the activities that this Stored Procedure performs.

Before the examples, I will remember the possible forms of EXECUTE AS on objects:

• CALLER: Run as CALLER is the default; If no other options are specified, the procedure will be performed in the security context of the caller.
• OWNER: Run as OWNER executes the procedure in the context of the object owner. If the object is created in a dbo-owned schema or by the database owner, the procedure runs with unlimited permissions.
• SELF: Run as SELF runs in the security context of the object creator. This is equivalent to running as specified user, where the specified user is the person who creates or changes the object.
• LOGIN / USER: As described above, you can also use a specific user (database level permissions) or login (instance level permissions) on the object header, allowing everyone who has EXECUTE permission on that object, such as a Stored Procedure, execute this object with the specified user / login context automatically.

The types of objects that may have the EXECUTE AS clause in the definition are:

• Stored Procedures - CALLER | SELF | OWNER | 'user_name'
• Functions (except inline table-valued functions) - CALLER | SELF | 'user_name'
• DDL Triggers with Database Scope - CALLER | SELF | 'user_name'
• DDL Triggers with Server Scope and logon triggers - CALLER | SELF | 'login_name'
• Queues - SELF | OWNER | 'user_name'
• Azure SQL Database - Stored Procedures - CALLER | SELF | OWNER | 'user_name'
• Azure SQL Database - Functions (except inline table-valued functions) - CALLER | SELF | OWNER | 'user_name'
• Azure SQL Database - DDL Triggers with Database Scope - CALLER | SELF | 'user_name'

To demonstrate a common use of EXECUTE AS in Stored Procedures, I will create an SP in msdb for running jobs by passing the job name as a parameter:

Now I will try to manually execute the sp_start_job:

and then try to run for this SP that I created:

By adding an EXECUTE AS clause to an object, you do not need to do anything for the user to have access to that SP, in addition to the usual EXECUTE permission in this Stored Procedure, for example. I will repeat the examples directly connected with the user "test", without using EXECUTE AS at any time:

And now, I will execute the procedure that I created and that will allow me to run any instance job, even without having permissions to it on my user:

Note: This stpInicia_Job SP has to be created in msdb for the way that permissions work in SQL Server. If you create a Stored Procedure and within it there are queries or commands on objects in other databases, the executing user must have explicit permissions on those objects even using EXECUTE AS. If everything inside the Stored Procedure only references objects from the database itself, the executor does not need to have any permissions on the objects involved within the SP, only EXECUTE permission on the Stored Procedure itself.

It is worth remembering that when you create an object with EXECUTE AS USER = 'User', deactivating or denying the connect privilege for the login associated with that user will NOT change the behavior of the use of that object, once the login actions end not influencing permissions at the database level in this situation (except in cases where the user is sysadmin, for example, and does not have explicit permissions on that object's database).

That is, if an SP has the clause WITH EXECUTE AS USER = 'User1' and you disable the login associated with that user, this will not cause an SP that has executed to generate an error when someone tries to execute it. But if you rename or delete that user in the bank, then SP will stop working.

Test # 1 - IMPERSONATE ANY LOGIN

To test the misused impersonate, I will take the same user as the example (test) and grant the IMPERSONATE ANY LOGIN privilege.

And now, I will connect in the instance with this user:

And let's try to select the table Person, which I applied a DENY SELECT to this user:

Well, he really doesn't have permission. What if we used another user to access the data? For example, the user “dirceu.resende”, which is sysadmin ..

Let's check which permissions he has access to after impersonate?

Result:

Hmm .. What if we tried to create a table? This “test” user only has the db_datareader permission.

Yes .. This user really can do everything that the impersonated user can do .. As I am impersonating a user member of the server role sysadmin, a common user can now do ANYTHING, even shut down the server! And does he want to remain an “ordinary” user?

Ready! Now he himself has become a sysadmin. If you want, you can even delete the other sysadmin users and “take” control of the instance for them. See how extremely dangerous this permission can be!

And it doesn't stop there. Look at another example of how this permission, when misused, can wreak havoc. Imagine that you, DBA, have a routine to log changes made to your database, such as the one I make available in the post. How to create an Audit trigger to log object manipulation in SQL Server.

Now imagine a malicious user using this to do wrong things and blame another colleague? Let's test .. First, I will create the table and change a column. All logs will be recorded using the user who performed the actions.

And now, to delete the table, I will use EXECUTE AS to delete as if it were another user.

Ready. I screwed up and still blamed it on my colleague. Want to solve it? Start using the ORIGINAL_LOGIN () function in your audit and log routines. To demonstrate this, I will change the trigger I created to create this audit:

Now, I will create the table again, change it and remove the login “dirceu.resende” to delete the table and leave the responsibility on his accounts:

Yeah, this time it didn't work. The ORIGINAL_LOGIN () function revealed the actual command executor, even with EXECUTE AS.

Test # 2 - Test with IMPERSONATE USER

Another test that we can try is to release the impersonate privilege on a specific user. For example, I want the “test” user to be able to use the EXECUTE AS command only as the “test2” user, both with low access level. For this, I will create the user “test2”, with the same permissions as “test”, but without the DENY of select in the Person table.

Created user, let's try to use EXECUTE AS from the “test” user:

Obviously, it was wrong. The “test” user does not have IMPERSONATE permission. Let's grant permission and try again:

Let's test it worked:

Oops! Now I am executing the commands as if it were the user “test2”. The “test” user is unable to query the Person table, as he has a DENY SELECT there. We will try to read the data through the user “teste2” with EXECUTE AS:

Oops! We were able to read the data in the table, even though the “test” user is not allowed to do so. Let's confirm if he doesn't succeed:

Yeah .. I can't, just with EXECUTE AS .. And can I use EXECUTE AS on another user I don't have access to?

Wonderful! I was unable to apply IMPERSONATE to another user, especially “dirceu.resende”, which is sysadmin .. Imagine the damage this could cause ..

An important point to consider in impersonate is when the instance has a resource limiter for certain users, which can be done using the Resource Governor on SQL Server.

What I want to test here is whether EXECUTE AS “cheats” the Resource Governor as well, managing to execute a query without being limited by the RG.

For this, I will create the Resource Pool, Workload Group, the classification function and enable the Resource Governor in the instance, limiting the resources that can be used by the “test” user: