SETUSER statement

Allows a user to assume the identity of (impersonate) another authorized user.

Syntax

{ SETUSER | SET SESSION AUTHORIZATION }
[ [ WITH OPTION ] userid ]

Parameters

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, SET SESSION AUTHORIZATION '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.

Remarks

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

Creating objects
You can use SETUSER to create a database object that is to be owned by another user.
Privilege checking
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.

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.

Standards

ANSI/ISO SQL Standard
The SET SESSION AUTHORIZATION syntax is part of optional ANSI/ISO SQL Language Feature F321, "User authorization". The SETUSER syntax is not part of the Standard. You can use the WITH OPTION syntax with both variants, but WITH OPTION is not part of the Standard.

Example

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.

SETUSER "Joe"
//  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
SETUSER