Solved

MS SQL 2000 : How to make the command xp_cmdshell to be executed by a non-sysadmin user?

Posted on 2007-11-22
5
2,391 Views
Last Modified: 2008-02-01
Hi,

I have a stored procedure that need to execute a batch file which located on the SQL server folder.  I do find a very useful command which can do this : master..xp_cmdshell.  However, no matter how I create a user, it still need the user which execute this command to be sysadmin.  However, according to the MS SQL online book, it says :
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
therefore, I think there should be a way to config a user which no need to be sysadmin role but still can execute master..xp_cmdshell as it says "...but can be granted to other users..."

Can anyone help ?

Thanks
Stanley
0
Comment
Question by:StanleyLMW
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20333703
you need to use the GRANT command. search for GRANT in SQL books online.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20333713
grant execute on xp_cmdshell to alogin

from the master db (no quotes, where alogin is a valid login)
0
 

Author Comment

by:StanleyLMW
ID: 20347830
Thanks imitchie,

I have tried, but still failed.  What I have done are as follow.

I login as "sa" in Query analyser and execute --> grant execute on xp_cmdshell to ABC <-- where ABC is a native SQL user (without any administrator role) which created with deafault DB to "master" and with public authority. (I do try to make user ABC with db_owner of master DB but still fail)

I login "ABC" in Query analyser and execute ---> exec xp_cmdshell 'dir' <--- and the following error returned.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1813 from GetProxyAccount on line 604
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Kindly please advise what's wrong/missing with my procedure ... thanks

Cheers
Stanley
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20347885
xp_cmdshell runs under the security context of the SQL Agent Proxy account
when executed by non-sysadmin users. This requires that you allow
non-sysadmin users to execute xp_cmdshell (uncheck the 'Only users with
sysadmin privileges...' checkbox under SQL Server Agent properties --> Job
System) and specify a Windows account for the SQL Agent proxy with the
permissions needed to run you application).

Furthermore, the SQL Server service account needs special permissions in
order to switch security context to the proxy account. These permissions
are assigned automatically when the service account is configured during
installation or changed with Enterprise Manager but not when the service
account is changed by other means. See "Service Accounts" in the SQL 2000
Books Online <instsql.chm::/in_overview_6k1f.htm> for details of the needed
permissions.
0
 

Author Comment

by:StanleyLMW
ID: 20348279
Thx a lot.  ^_^
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now