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,400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
UPDATE JOIN multiple tables 5 35
Error in sql query statment. 21 70
SQL Query - Multiple match on field with no extras 7 36
What is GIS method of Geometry data type? 6 33
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 article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

734 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