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

Views: 3.143
Reading Time: 23 minutes

Hey guys!
In this article I would like to share with you a feature available since SQL Server 2005, which allows the execution of T-SQL codes on behalf of another user. I'm talking about the EXECUTE AS command.

With this high demand for security that companies are looking for, driven by demands for GDPR compliance, organizations are often taking this opportunity to review all security, auditing, and monitoring, and that's one of the reasons that motivated me to write this article.

If you want to delve into more security articles, be sure to click this link here to visit my other articles on this subject.

If you are experiencing a security issue or would like expert advice to review, test and apply security best practices for your business, feel free to contact me this link here.

Introduction

By default, all operations during a session are subject to permission checks for that user. When an EXECUTE AS statement is executed, the session execution context is switched to the login or specific username. After context switching, permissions are checked at the user's logon and security tokens for the account instead of the person calling the EXECUTE AS statement.

I will demonstrate how this works by creating a user named “test,” with permission from db_datareader in the AdventureWorks database, but reading denied in the Person.Person table:

After running the above script, I will check which user is currently in the context of my session:

Result:

Now, I will use the EXECUTE AS statement to change the current user of my session:

Result:

Since this user is in the database role “db_datareader”, he should have no problem trying to query the data from the Address table, for example:

But if we try to query data from the Person table, which I applied a DENY SELECT to this user, he will not be able to execute the command. Remember that my original user is sysadmin, ie he has no access restrictions.

With that, I was able to demonstrate a very interesting form of one of the purposes of EXECUTE AS, which is validate if a particular user can perform an activity after the DBA has granted a permission, For example.

Impersonation Types

View content
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.

Remember 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 that user's sysadmin “powers” ​​because EXECUTE AS USER only inherits the permissions at the database level. Since you are already db_owner, you will not inherit any new permissions you do not already have, you can only execute commands as another user (which is still a risk). If you can run EXECUTE AS LOGIN from a sysadmin user, then you will have all the instance-level “powers” ​​he has (ie you 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.

Rolling back EXECUTE AS and returning to the original user

View content
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:

RUN 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:

RUN 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:

What are the permissions to use EXECUTE AS?

View content
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 noteworthy that there is also the command GRANT IMPERSONATE ON LOGIN / USER :: [username1] TO [username2], which allows you to grant IMPERSONATE access to specific users. In addition, members of the database role db_owner can use EXECUTE AS on users created in these databases. Remember that even if you are impersonating a sysadmin user through a database that you are db_owner, for example using EXECUTE AS USER, you will not have the sysadmin “powers” ​​of that user because EXECUTE AS User only inherits permissions at the database level. Since you are already db_owner, you will not inherit any new permissions 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

Scroll to SQL Server “Administrator”. Users in this role can do anything in the database, so they 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, so they already have IMPERSONATE LOGIN, IMPERSONATE USER, and IMPERSONATE ANY LOGIN permission, but unlike role sysadmin, these users CAN be denied IMPERSONATE privileges 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:

Who is allowed to run EXECUTE AS?

View content
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:

RUN AS on database objects

View content
A very common practice when using EXECUTE AS is to use it on bank objects, such as Stored Procedures, to set the default permission that these objects will be executed on. That is, you can create a Stored Procedure that will always be executed as the “dirceu.resende” user who is a member of the server role sysadmin and therefore has permission to do anything, and anyone who has EXECUTE access to that Stored Procedure You can do it without having to have IMPERSONATE ANY LOGIN privilege 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 Login 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 don't have to do anything for the user to have access to that SP, besides the usual EXECUTE permission on this Stored Procedure, for example. I will repeat the examples connected directly 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.

Remember that when you create an object with EXECUTE AS USER = 'User', disabling or denying the connect privilege for login associated with that user will NOT change the behavior of using that object, as login actions end up not influencing database-level permissions 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 WITH EXECUTE AS USER = 'Username1' clause and you disable the login associated with that user, it will not cause an SP that has run to generate an error when someone tries to run it. But if you rename or delete this user in the database, then SP will stop working.

Safety Tests in Audit Routines

View content

#1 Test - 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 isn't allowed. 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 alter a table? This "test" user only has the db_datareader permission.

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

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

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 a table again, change it and impersonate the login “dirceu.resende” to delete the table and leave the responsibilities 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 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 "teste2", with the same permissions as "test", but without the select DENY in the Person table.

User created, let's try to use EXECUTE AS from user “test”:

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

Let's test it worked:

Oops! Now I am executing the commands as if I were the user "teste2". The "test" user cannot make queries to the Person table as he has a DENY SELECT there. Let's try to read the data through user “teste2” with EXECUTE AS:

Oops! We were able to read the data from the table even though the "test" user was not allowed to do so. Let's confirm that he can't:

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 could not apply IMPERSONATE to another user, especially “dirceu.resende”, which is sysadmin .. Imagine the damage this could cause ..

EXECUTE AS and Resource Governor

View content
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 if EXECUTE AS "tricks" Resource Governor too, being able to execute a query without being limited by 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:

After creating and activating Resource Governor, I will perform a heavy query to test the resource constraints:

As the query runs, I'll look at the Query to return running queries (sp_WhoIsActive without consuming TempDB) which profile is being used for this query:

Well The query using the “test” user was using the Resource Governor's “Group” workload group, which has IOPS, CPU, and memory limitations as I set it up. I will create the user “dirceu.resende” for the sysadmin login “dirceu.resende” in the AdventureWorks database and release the IMPERSONATE permission on this user for the “test” user:

Now let's try using EXECUTE AS USER for a user who doesn't have this limitation, like the “dirceu.resende” user:

Not surprisingly, Resource Governor was not “fooled” by EXECUTE AS USER and continued to limit the “test” user even disguised as “dirceu.resende” as it inherits only database-level “powers” ​​and not of the instance. Now what happens if I try to run EXECUTE AS LOGIN? Lets test!

And to my surprise, even though it received the powers of a sysadmin, Resource Governor continued to limit user resources "test", even using the user EXECUTE AS LOGIN "dirceu.resende" (and yes, I opened a new one). session to test):

And now, I will execute the query with the user “dirceu.resende”, without using impersonate, which will show us that Resource Governor is not now limiting the resources of this session (default pool):

Taking up references

That's it folks!
Big hug and see you!