SETUSER Statement

Allows a database owner to impersonate another user. The SETUSER statement is used by the system administrator or a database owner when he or she wants to adopt the identity of another user in order to use another user's database object, to grant permissions to that object, to create an object, and so on.

Syntax

SETUSER ['username' [WITH NORESET]]

where

username: Specifies the name of the user who will be impersonated by the system administrator or the database owner. The username supplied must be listed in the sysusers table. When no username is specified, the original identity of the system administrator or database owner is re-established.
NORESET: Specifies that subsequent SETUSER statements (with no specified username) will not reset to the system administrator or database owner.

Remarks

Using SETUSER to impersonate a user, a database owner, or system administrator will have only those rights given to the user being impersonated. Because the owner of an object always retains the rights to that object, no users, other than those given explicit permission to that object, can access it. Because a database owner and the system administrator always have supervisory permission over their database (or the system) they can temporarily adopt another database user's identity. The database owner or system administrator can act as that user, create objects in that user's name, and test permissions as given to that user. The SETUSER statement remains in effect until another SETUSER statement is issued or until the current database is changed with the USE statement.

In SQL Server 6.0, the SETUSER statement does not need to be used to drop objects owned by another user. The database owner and system administrator can drop user's objects by explicitly qualifying the object name with the owner's name. For details, see the DROP statements (DROP DEFAULT, DROP PROCEDURE, DROP TABLE, and so on).

In earlier releases of SQL Server, the system administrator retained full system administrator permissions and could not use SETUSER to acquire another user's permissions. If you want to maintain compatibility with this previous behavior, use trace flag 206. For details, see the Trace Flags topic.

Permission

Execute permission defaults to the database owner and is not transferable.

Examples

A. SETUSER

In this example, Mary (the database username of mary) has created a table called computer_types. Mary happens to be out of town and another user (Joe) needs to access Mary's computer_types table. The database owner or system administrator can execute:

SETUSER 'mary'
go
GRANT SELECT ON computer_types TO joe
go
SETUSER

B. NORESET Option

In this example, the database owner needs to create some objects and then test their usability with minimal permissions. For simplicity, the database owner wants to maintain only Mary's permission for the entire session. The database owner or system administrator can execute:

SETUSER 'mary' WITH NORESET
go
CREATE TABLE computer_types2
.
.
.
GRANT ...
go
SETUSER        /* This statement will have no effect. */

The only way the database owner or system administrator can re-establish his or her own rights is to log off and then log on again.