SETUSER or SET SESSION AUTHORIZATION
SETUSER and SET SESSION AUTHORIZATION are semantically equivalent. However, the value you specify for SETUSER must be
formatted as an identifier (for example, SETUSER JoeS or SETUSER "JoeS"), whereas the value you specify for SET SESSION AUTHORIZATION must be a formatted as a string (for example, SETUSER 'JoeS').
WITH OPTION clause
During an impersonation session, database option settings in effect for the impersonator may be set differently than
those of userid, which can impact results. Specify WITH OPTION to change the database options to reflect the options in effect for userid.
The SETUSER statement is provided for administrative use and should not be used for connection pooling. After executing a
SETUSER statement, you can execute one of the following commands to verify which user authorization you have assumed:
SELECT CURRENT USER
SETUSER with no user ID undoes all earlier SETUSER statements.
A successful impersonation remains in effect until it is manually terminated (by executing a SETUSER statement with no ID)
or the session is terminated.
The SETUSER statement cannot be used inside a procedure, trigger, event handler or batch.
There are several uses for the SETUSER statement, including the following:
You can use SETUSER to create a database object that is to be owned by another user.
By acting as another user, with their privileges and inheritances, a user can test the privileges and name resolution
of queries, procedures, views, and so on.
Providing a safer environment for administrators
The database administrator has permission to perform any action in the database. To ensure that you do not accidentally
perform an unintended action, use SETUSER to switch to a different user ID with fewer privileges.
You must have the SET USER system privilege. However, your ability to successfully execute a SETUSER statement (start an impersonation
session) depends on whether you meet the at-least criteria for the person you are attempting to impersonate. The SETUSER statement
fails if this condition is not met.
The SET SESSION AUTHORIZATION syntax is part of optional SQL/2008 language feature F321, "User authorization". The SETUSER
syntax is a vendor extension. You can use the WITH OPTION syntax with both variants, but WITH OPTION is a vendor extension.
In the first statement in this example (SETUSER "Joe"), a user who has the SET USER system privilege impersonates Joe to run some operations using Joe's privileges. In the second
statement (SETUSER WITH OPTION "Jane"), the user impersonates Jane to perform some operations using Jane's privileges and the database options currently in effect
for Jane. In the third statement (SETUSER), the user reverts back to their own user ID, privileges, and database options.
// Some operations are run using Joes privileges ...
SETUSER WITH OPTION "Jane"
// Some operations are run using Jane's privileges, and the
// database options in effect are changed to the current
// database options for Jane