• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2407
  • Last Modified:

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

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
StanleyLMW
Asked:
StanleyLMW
  • 3
  • 2
1 Solution
 
imitchieCommented:
you need to use the GRANT command. search for GRANT in SQL books online.
0
 
imitchieCommented:
grant execute on xp_cmdshell to alogin

from the master db (no quotes, where alogin is a valid login)
0
 
StanleyLMWAuthor Commented:
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
 
imitchieCommented:
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
 
StanleyLMWAuthor Commented:
Thx a lot.  ^_^
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now