SETUSER statement

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

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

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.

SQL/2008 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.

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

SETUSER statement

Syntax

Parameters

Remarks

Privileges

See also

Standards and compatibility

Example