Permission to run master..xp_cmdshell

Hi!
I have a user (SQL login), who has access to master database and the
right to run xp_cmdshell via database role membership. When running
it:
SETUSER 'Smith'
go
exec master..xp_cmdshell 'dir c:'
go
SETUSER
=============================== We get:
Msg 50001, Level 1, State 50001
xpsql.c: Error 997 from GetPassword on line 465
When I give the user the sa server role, it works fine. I am not very
happy to give so many users the sa role. Please help me to figure out,
how to allow a user to run xp_cmdshell.
/* I suspect that this is the consequences? of a recent security patch
installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
complains about it before */
Thanks.I assume you are running SQL 7. This error may indicate a problem with
the proxy and/or service accounts.
To ensure the proxy account is configured properly, use Enterprise
Manager to navigate to Management --> SQL Server Agent --> Job System
and ensure 'only allow users with Sysadmin privileges ...' is unchecked
and click the Reset Proxy Account button.
If the problem persists, ensure the SQL Server service account has the
needed permissions. The easiest way to do this is to re-specify the
service account using Enterprise Manager (server properties -->
Security). Assuming you are using a domain account, change it to the
System account and then back to the domain account. Enterprise Manager
will assign the domain account the necessary rights during the change.
You'll need to restart SQL Server after the change. Sometimes a reboot
is required as well.
Separately, you might consider leveraging cross database ownership
chaining so that you don't need to grant direct execute permissions to
users on xp_cmdshell. If your proc is owned by dbo, all you need to do
is change ownership of your user database to the 'sa' login. Due to the
unbroken ownership chain, users can then execute xp_cmdshell only via
your user proc and cannot execute it directly. However, you should
employ this technique only if you fully trust users that have
permissions to create dbo-owned objects in your user database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0308192241.7a70bf5f@.posting.google.com...
> Hi!
> I have a user (SQL login), who has access to master database and the
> right to run xp_cmdshell via database role membership. When running
> it:
> SETUSER 'Smith'
> go
> exec master..xp_cmdshell 'dir c:'
> go
> SETUSER
> ===============================> We get:
> Msg 50001, Level 1, State 50001
> xpsql.c: Error 997 from GetPassword on line 465
> When I give the user the sa server role, it works fine. I am not very
> happy to give so many users the sa role. Please help me to figure out,
> how to allow a user to run xp_cmdshell.
> /* I suspect that this is the consequences? of a recent security patch
> installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
> complains about it before */
> Thanks.