SETUSER (T-SQL)

Allows a member of the sysadmin fixed server role or db_owner fixed database role to impersonate another user.

Important SETUSER is included in Microsoft® SQL Server™ version 7.0 only for backward compatibility, and is not recommended to be used. SETUSER may not be supported in a future release of SQL Server.

Syntax

SETUSER ['username' [WITH NORESET]]

Arguments

'username'

Is the name of a SQL Server or Microsoft Windows NT® user in the current database that is impersonated. When username is not specified, the original identity of the system administrator or database owner impersonating the user is reestablished.

WITH NORESET

Specifies that subsequent SETUSER statements (with no specified username) do not reset to the system administrator or database owner.

Remarks

SETUSER can be used by members of the sysadmin or db_owner roles to adopt the identity of another user in order to test the permissions of the other user.

Only use SETUSER with SQL Server users. SETUSER cannot fully reflect the permissions of Windows NT users. When SETUSER is used to assume the permissions of a Windows NT user, the permissions of the Windows NT groups that contain the user are not included.

When SETUSER has been used to assume the identity of another user, any objects that are created are owned by the user being impersonated. For example, if the database owner assumes the identity of user Margaret and creates a table called orders, the orders table is owned by Margaret not the system administrator.

SETUSER is not required to create an object owned by another user, because the object can be created with a qualified name that specifies the other user as the owner of the new object. For example, if user Andrew, who is a member of the db_owner database role, creates a table Margaret.customers, user Margaret owns customers, not user Andrew.

SETUSER remains in effect until another SETUSER statement is issued or until the current database is changed with the USE statement.

Permissions

SETUSER permissions default to members of the sysadmin fixed server role or db_owner fixed database role, and are not transferable.

Examples

A. Use SETUSER

This example shows how the database owner can adopt the identity of another user. User mary has created a table called computer_types. Using SETUSER, the database owner impersonates mary to grant user joe access to the computer_types table.

SETUSER 'mary'

go

GRANT SELECT ON computer_types TO joe

go

SETUSER

B. Use the NORESET option

This example shows how a database owner must create some objects and then test their usability with minimal permissions. For simplicity, the database owner wants to maintain only the permission granted to mary for the entire session.

SETUSER 'mary' WITH NORESET

go

CREATE TABLE computer_types2

.

.

.

GRANT ...

go

SETUSER        /* This statement has no effect. */

Note If SETUSER WITH NORESET is used, the database owner or system administrator must log off and then log on again to reestablish his or her own rights.

See Also