Setuser failed?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gurbao - 30 Jan 2007 09:07 GMT

Hi,

I have recently migrated from sql server 2000 to 2005.
One of my old jobs is currenty failing with

Msg 15157, Sev 11, State 1: Setuser failed because of one of the
following reasons: the database principal 'sa' does not exist, its
corresponding server principal does not have server access, this type
of database principal cannot be impersonated, or you do not have
permission. [SQLSTATE 42000]

The jobs calls a procedure that again uses sp_makewebtask. It is this
proc that throws the above error.

Someone with a clue on what I should do to resolve this?

Regards,

Reply to this Message

Erland Sommarskog - 30 Jan 2007 23:02 GMT

> I have recently migrated from sql server 2000 to 2005.
> One of my old jobs is currenty failing with
[quoted text clipped - 9 lines]
>
> Someone with a clue on what I should do to resolve this?

Both SETUSER and sp_makewebtask are deprecated, so in the long run, you
should probably look into to replace the code.

It wasn't clear to me: who is doing SETUSER on 'sa'? sp_makewebtask
or the procedure that calls it? If the latter, can you post the code?

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

yes - 22 Aug 2008 10:30 GMT

You not answered the question. You said nothing...

i have 2 sql2005 with sp2
on the first i can use setuser 'sa' on the second can't, with the same error.
why need it?
because i use With execute as owner in procedures and i have another problme with xp_cmdshell

if i run xp_cmdshell its works, but in the SP not, i would like to try in he name of SA, i type
setuser 'sa'
exec master..xp_cmdshell 'dir c:\'
setuser

but setuser failed..

From http://www.developmentnow.com/g/116_2007_1_0_0_927529/Setuser-failed.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Reply to this Message

Erland Sommarskog - 22 Aug 2008 22:50 GMT

> i have 2 sql2005 with sp2 on the first i can use setuser 'sa' on the
> second can't, with the same error. why need it?
[quoted text clipped - 8 lines]
>
> but setuser failed...

SETUSER is deprecated. Don't use it, use EXECUTE AS LOGIN = 'sa' instead.
Note that for the command to be possible, you need to have impersonation
rights on 'sa'.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message