SQL Server Impersonation

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 MyDomainAppAcct. 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 (userlogin) 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

»


See All Articles by Columnist
Deanna Dicken

Deanna Dicken
Deanna Dicken
Deanna Dicken is an architect with approximately 20 years of IT experience. In that time she has worked on many large-scale, mission-critical applications and has been involved at all phases of the lifecycle. She has also contributed to three SQL Server MCSE books, co-authored Learn SQL in a Weekend, and tech edited many other titles. She lives on the outskirts of Indianapolis, Indiana with her husband, Curtis, and children, Kylee and Zach.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles