Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,402 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

'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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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