Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
2,403 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

927 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