SETUSER
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™ 2000 only for backward compatibility, and its usage is not recommended. 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. It is not supported with Windows users. 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 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.