Posted Feb 12, 2010

SQL Server Impersonation

By Deanna Dicken

SQL Server impersonation, or context switching, is a means to allow the executing user to assume the permissions of a given user or login until the context is set back, set to yet another user, or the session is ended. Deanna Dicken shows you two mechanisms for accomplishing this task and walks through some examples.

Introduction

SQL Server impersonation, or context switching, is a means to allow the executing user to assume the permissions of a given user or login until the context is set back, set to yet another user, or the session is ended. In the following sections we will discuss two mechanisms for accomplishing this task and walk through some examples.

SETUSER

If you have cause to require context switching in SQL Server 2000, you have but one choice: SETUSER. Execution of this statement with a provided username temporarily sets the execution permissions to that of the supplied account. Multiple calls can be made and the context will continue to switch until reverted back to the original context by calling SETUSER without a username.

Permissions

Contrary to Books Online, an account wishing to call SETUSER must have the sysadmin server role. Books Online mistakenly states users with dbo rights can also utilize this statement, however this does not work.

Examples

The following example shows context being switched from the logged in account, adminacct in this case, to a specific SQL login, to another SQL user, and back again. Notice how the context reverts to the logged in account with the first execution of SETUSER without a username.

SETUSER 'jdoe' SELECT SUSER_SNAME() SETUSER 'jschmoe' SELECT SUSER_SNAME() SETUSER SELECT SUSER_SNAME()

Results:

----------------------- jdoe (1 row(s) affected) ----------------------- jschmoe (1 row(s) affected) ----------------------- adminacct (1 row(s) affected)

Its important to note that in the above example jdoe must have the sysadmin role for the SETUSER jschmoe command to complete successfully. If jdoe does not have sysadmin, the following error is thrown.

Msg 15157, Level 16, State 1, Line 3 Setuser failed because of one of the following reasons: the database principal 'jschmoe' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

SETUSER has an optional directive WITH NORESET, which can be used with the above example to cause the impersonation to not revert to the sysadmin that kicked it off. So again, logged in as adminacct, we kick off the following.

SETUSER 'jdoe' WITH NORESET SELECT SUSER_SNAME() SETUSER 'jschmoe' SELECT SUSER_SNAME() SETUSER SELECT SUSER_SNAME()

Results:

----------------------- jdoe (1 row(s) affected) ----------------------- jschmoe (1 row(s) affected) ----------------------- jdoe

The results show that the context did not revert to the original account, but did revert to the previous context jdoe even though that account also has sysadmin. To revert the context all the way back to the adminacct, the session will have to be closed and a new one opened.

EXECUTE AS

Starting with SQL Server 2005, the EXECUTE AS statement has taken the place of SETUSER for context switching. SETUSER is deprecated and should not be used in SQL Server 2005 or later.

There are two incarnations of EXECUTE AS, EXECUTE AS LOGIN and EXECUTE AS USER. The former allows the calling account to take on the server level permissions of the login such as securityadmin. The latter, provides access to the database level permissions of the passed in username. To end the impersonation, the REVERT statement can be issued or the session terminated.

EXECUTE AS LOGIN

Should you have a particular need to impersonate a login and require its server level permissions, you will need to use the EXECUTE AS LOGIN. In the following example, assume we are logged in as adminacct, which has the sysadmin role. We have a list of requests for new logins to be created in one of the companys many databases. Because of some auditing procedures we have in place, we need to use a particular login, securacct, which has the securityadmin server role. Rather than hunting down the password and logging in as this account, we can just execute our stored procedure to create these logins as securacct.

EXECUTE AS LOGIN = 'securacct' EXEC spCreateNewLogin 'jdoe' REVERT

EXECUTE AS USER

If your needs are contained to the permissions at the database level, EXECUTE AS USER will do just fine. An example of this is a system account that logs into the database, but needs to impersonate the user that actually kicked of the transaction in the front-end application. The context of the transaction can be switched to the executing users username. In this case, the system account need only have dbo rights in the database.

Say the system account thats logging in on behalf of the user is MyDomain\AppAcct. The SQL username of the user that initiated the transaction is jdoe. The system account logs into the SQL Server database and issues the following:

EXECUTE AS USER = 'jdoe' EXEC spSaveOrderItem 'B123', 1, '9484844' REVERT

Issuing the REVERT statement at the end of the transaction allows the context to switch back to the system account in this case. In the case of connection pooling, this would ensure that the session opened by the system account is consistently impersonating the application user and then switching back to itself for the user whose transaction is next in line for that session.

As with SETUSER, it is possible to constrain the impersonation to prevent it from returning the context to the original account. To do this with EXECUTE AS, add WITH NO REVERT to the end of the statement as below. The REVERT at the end has no effect on the context.

EXECUTE AS LOGIN = 'jdoe' WITH NO REVERT EXEC sp_SaveOrderItem 'B123', 1, '9484844' REVERT

Permissions

Whereas SETUSER required sysadmin permissions, EXECUTE AS USER requires just dbo permissions to execute the statement. EXECUTE AS LOGIN requires the sysadmin server role since the permissions you wish to impersonate are at the server level.

An alternative to granting these roles to UserA who has a legitimate reason to impersonate UserB is to have UserB explicitly grant impersonation rights to UserA.

GRANT IMPERSONATE ON USER::UserB TO UserA

Now UserA can use EXECUTE AS USER = UserB before his statements without being granted the dbo database role. Similarly, this can be done for a login if server level permissions are needed. In this example, LoginD is going on vacation and temporarily needs to grant LoginC his abilities as a securityadmin without divulging his password. When he returns from vacation, he can simply execute a REVOKE to remove the impersonate permission.

GRANT IMPERSONATE ON LOGIN::LoginD TO LoginC

Impersonation across Linked Servers

Impersonation can get a little tricky when the task at hand crosses SQL Server linked servers. If impersonation is being handled using SETUSER, a call that crosses linked servers will fail. The context needs to be reverted to the original user prior to crossing servers.

With the introduction of EXECUTE AS, crossing link servers as an impersonated user or login becomes a possibility. To make this happen, the principle (user\login) needs to be recognized on the linked server. Additionally, the linked servers need to be trustworthy. To make the databases trustworthy, you will need to set their TRUSTWORTHY property to TRUE by issuing an ALTER DATABASE command such as this:

ALTER DATABASE mydatabase SET TRUSTWORTHY ON

Conclusion

When working in SQL Server 2000, SETUSER is your option for implementing impersonation (or context switching). Be aware of the elevated rights needed by the account executing SETUSER and the security risk involved. Its also important to note the limitation on linked server usage.

SQL Server 2005 and above offer a better context switching method in EXECUTE AS. Understanding the requirements for impersonation will help determine if the impersonation is at the server level (the login) or the database level (the user). If the impersonation is one off, consider the use of GRANT IMPERSONATE instead of elevating privileges for the logged in account.

For More Information

The following links can provide additional information for the concepts covered here:

EXECUTE AS - http://msdn.microsoft.com/en-us/library/ms181362.aspx

REVERT - http://msdn.microsoft.com/en-us/library/ms178632.aspx

SETUSER - http://msdn.microsoft.com/en-us/library/ms186297.aspx

TRUSTWORTHY - http://msdn.microsoft.com/en-us/library/ms187861.aspx