SetUser - With NoReset

SetUser - With NoReset

Post by fion » Sun, 27 Apr 2003 14:10:59



Hi all,

Could anyone tell me the usage of With NoReset?  I have read BOL but still
don't quite understand.

Thanks!

 
 
 

SetUser - With NoReset

Post by Gwyn » Sun, 27 Apr 2003 15:03:20


Hi Fiona,

WITH NORESET only has an affect when you have multiple
calls to SETUSER.

e.g. 1 you login as a sysadmin user. You then call
SETUSER 'Billy' to imperonate the user Billy. The SQL
statements you execute next will be as if Billy himself
was executing the statements.

After you no longer want to impersonate Billy you can call
SETUSER again. This will cancel the impersonation so that
any subsequent SQL statements would be executed under your
original security context.

e.g. 2 you login as a sysadmin user. You then call
SETUSER 'Billy', WITH NORESET to impersonate Billy. This
works the same as earlier in that your commands will be
run as if Billy were executing them.

If you were to call SETUSER again without any parameters
you would no longer revert your security back to dbo. The
call to SETUSER will have no effect because the WITH
NORESET option was used. In order to cancel the
impersonation of Billy you will have to close the
connection and open a new one.

Cheers,
Gwynn

 
 
 

SetUser - With NoReset

Post by fion » Sun, 27 Apr 2003 16:58:37


Gwynn, Thanks!


Quote:> Hi Fiona,

> WITH NORESET only has an affect when you have multiple
> calls to SETUSER.

> e.g. 1 you login as a sysadmin user. You then call
> SETUSER 'Billy' to imperonate the user Billy. The SQL
> statements you execute next will be as if Billy himself
> was executing the statements.

> After you no longer want to impersonate Billy you can call
> SETUSER again. This will cancel the impersonation so that
> any subsequent SQL statements would be executed under your
> original security context.

> e.g. 2 you login as a sysadmin user. You then call
> SETUSER 'Billy', WITH NORESET to impersonate Billy. This
> works the same as earlier in that your commands will be
> run as if Billy were executing them.

> If you were to call SETUSER again without any parameters
> you would no longer revert your security back to dbo. The
> call to SETUSER will have no effect because the WITH
> NORESET option was used. In order to cancel the
> impersonation of Billy you will have to close the
> connection and open a new one.

> Cheers,
> Gwynn

 
 
 

1. Windows NT User activated by SETUSER error

Compiled a stored procedure containing a select statement that points at a
view on a linked server (link using sa). The select statement works fine in
QA outside of the SP, but when I try to execute the SP, I get the "remote
access not allowed for Windows NT User activated by SETUSER" error.
MS support online says this can happen with jobs when the owner is not sa,
but I'm not even getting that far (obviously I will be creating a job and
scheduling it when I can get past this error).
Both the view and the SP are owned by the dbo; as I said, the link is using
sa (same u/name & password on both servers), and the statement works when
run manually through Query Analyser.

--

Regards

Paul Paintin

2. Error 3218 with MDB File on Novell Netware

3. SETUSER permission denied

4. $70,000+ I4GL/PA in Philly,PA (PT telecommute)

5. can not begin tran after 'setuser'

6. DBMS_ALERT

7. SETUSER

8. codepage errors

9. Scheduled Tasks fail: Unable to perform SETUSER...

10. SETUSER / OWNER

11. SETUSER alternative

12. Remote access not allowed for Windows NT user activated by SETUSER

13. setuser