Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize
1 out of 2 rated this helpful - Rate this topic

SETUSER

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Allows a member of the sysadmin fixed server 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 impersonating the user is reestablished.

WITH NORESET

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

Remarks

SETUSER can be used by members of the sysadmin fixed server role 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 system administrator 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 system administrator can adopt the identity of another user. User mary has created a table called computer_types. Using SETUSER, the system administrator 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 system administrator must create some objects and then test their usability with minimal permissions. For simplicity, the system administrator wants to maintain only the permissions 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 system administrator must log off and then log on again to reestablish his or her own rights.

See Also

DENY

GRANT

REVOKE

USE

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.