SETUSER statement

Allows a database administrator to assume the identity of another authorized user on the same connection.

Syntax

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

Parameters

WITH OPTION clause By default, only permissions (including group membership) are altered. If WITH OPTION is specified, the database options in effect are changed to the current database options of userid.
userid The user ID is an identifier (SETUSER syntax) or a string (SET SESSION AUTHORIZATION syntax).

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.

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.
Permissions checking By acting as another user, with their permissions and group memberships, a database administrator can test the permissions 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. If you want to ensure that you do not accidentally perform an unintended action, you can use SETUSER to switch to a different user ID with fewer permissions.

Note

The SETUSER statement cannot be used within procedures, triggers, events, or batches.

Permissions

Standards and compatibility

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.

Example

The following statements, executed by a user named DBA, change the user ID to be Joe, then Jane, and then back to DBA.

SETUSER "Joe"
// ... operations...
SETUSER WITH OPTION "Jane"
// ... operations...
SETUSER

The following statement sets the user to Jane. The user ID is supplied as a string rather than as an identifier.

SET SESSION AUTHORIZATION 'Jane';

Discuss this page in DocCommentXchange.