xpsql.cpp: Error 997 from GetProxyAccount on line 472

[David Lightman Robles]

I recently posted a message querying for help creating a file from SQL
Server 2000 that did not use xp_cmdshell because of securty issues. As a
first approach, and not taking care of it, I have granted public group to
execute xp_cmdshell so that they could do:
xp_cmdshell 'echo ' + @message + '>' @FileName

I thought that granting execution rights to that XP would be enough, but it
seems it is not. Now, everytime a user (not administrator) tries to execute
that XP they get the following error:

xpsql.cpp: Error 997 from GetProxyAccount on line 472

What should I do to allow my users execute that XP and hence, be able to
write out a simple text file ?

Thanks in advance.

[Alasdair Cunningham-Smith]

First, you need to set up a proxy account:

See BOL on xp_cmdshell and xp_sqlagent_proxy_account or go into SQL
Enterprise Mangler, right click on SQL Agent, select properties, "Job
System" tab. Clear the "Only users with sysadim privileges ..." checkbox and
pick an account.

I'd also not grant the users direct access to xp_cmdshell, but write a
stored procdeure to do what you need and grant the users rights to that.

HTH

Cheers,

Alasdair Cunningham-Smith
MCSD MCSE MCDBA

"David Lightman Robles" <nospam_dlig...@iname.com_nospam> wrote in
message news:uinmn6PTBHA.432@tkmsftngp07...

[David Lightman Robles]

I have already written a SP to avoid accessing xp_cmdshell directly and
granted users rights to it... but at the end, my SP needs to execute
xp_cmdshell and if I don't grant users rights to it too they are not able to
execute mine either. Reading your comment, it seems (at least you think) it
is possible to execute xp_cmdshell from another SP without having explicit
rights over xp_cmdshell... is this right? Please tell me if that can be
done.

Thanks.

"Alasdair Cunningham-Smith" <alasdair@euphony-dot-net> escribió en el
mensaje news:trpjgk1...@corp.supernews.com...

[Alasdair Cunningham-Smith]

<<<
Reading your comment, it seems (at least you think) it
> is possible to execute xp_cmdshell from another SP without having explicit
> rights over xp_cmdshell... is this right? Please tell me if that can be
> done.
>>>

Look up "ownership chains" in the index in BOL.

Providing you create the wrapper proc as dbo (log in as sa or member of
SysAdmins fixed server rolee) you can don't need to grant users direct
access to xp_cmdshell. That covers offering users restricted access to any
other dbo owned object in fact.

However, xp_cmdshell internally finds out whether it was called by a member
of SysAdmins, and if not checks the proxy account set by
xp_sqlagent_proxy_account (which you hadn't yet set, hence the xpsql.cpp
error) and uses NT system calls (ImpersonateUser) to run the command in the
specified account.

Clear as mud now, I'm sure.

--
Cheers,

Alasdair Cunningham-Smith
MCSD MCSE MCDBA

"David Lightman Robles" <nospam_dlig...@iname.com_nospam> wrote in

message news:OFLIScWTBHA.1608@tkmsftngp07...

[David Lightman Robles]

Thanks for your help. Since I did not have my 'wrapper proc' in the master
database, SQL Server checked permissions in both my SP and xp_cmdshell (in
master). Now I have moved my SP to master database and just granted rights
to it (and not to xp_cmdshell too), and it works perfectly. I dont even need
to use the proxy account for SQL Server Agent jobs.

Thanks again.

"Alasdair Cunningham-Smith" <alasdair@euphony-dot-net> escribió en el

mensaje news:trr92tl...@corp.supernews.com...