Solved

Running xp_cmdshell as a non-admin

Posted on 2008-06-18
6
516 Views
Last Modified: 2012-06-27
Hi all,

I have the following stored procedure in SQL Server 2000:

CREATE PROCEDURE [dbo].[sp_CSV_COPROD] AS
BEGIN
DECLARE @bcpCommand VARCHAR(8000)

SET @bcpCommand = 'bcp partman.dbo.vw_export_coprod out \\SQLSVR1\c$\Co-Products.csv -S SQLSVR1 -T  -c -t,'
EXEC master..xp_cmdshell @bcpCommand
            
END
GO

I can run this fine and it outputs the required csv file (this is because I am an admin). However, when a non admin user runs this sp they get an error about permissions for xp_cmshell. I am using windows authentication.
Can someone please explain the best way to go about allowing non-admin users permissions to running this? I don't particularly want to give my Domain Users windows group execute permissions on the master db.

I am a COMPLETE NOVICE at this so hand holding may be required!

Thanks
0
Comment
Question by:FMabey
[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
6 Comments
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21811457
Although xp_cmdshell is a useful tool for administrators, it's really unsecure to give access to it to the common users. Also, the command you are running uses an administrative share on the server, so the user would have to have administrator rights on the box inorder to be able to execute it anyway.....

This website explains why it's so bad and gives you some alternatives.

http://blogs.msdn.com/sqlsecurity/archive/2008/01/10/xp-cmdshell.aspx

0
 
LVL 2

Expert Comment

by:climbingjaffa
ID: 21811568
If you have to use xp_cmdshell you can setup a windows user account and from that a proxy sql login and user, that has the rights to execute xp_cmdshell ....

so when a user trys to run xp_cmdshell but they don't have the rights then SQL Server will use the proxy ....This avoids having to give a user sysadmin rights.....

Check out this out thread on EE its down the road you wantto travel
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22118390.html

But as was said in the previous comment using xp_cmdshell its not the most favourable approach from a security context. but alot of enterprise systems do use it cause it offers flexibility

0
 
LVL 3

Author Comment

by:FMabey
ID: 21811622
Chrisedebo,

Isn't the link you sent me giving alternatives for SQL 2005?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Author Comment

by:FMabey
ID: 21811684
As I am running this stored procedure through a VB.NET application, could I not connect them as a sysadm, then disconnect once the procedure has run. That way they wont have rights to use xp_cmdshell.
0
 
LVL 7

Accepted Solution

by:
orcic earned 400 total points
ID: 21811900
If this is not a time critical operation I have a tip:
Create a table in your database that would hold your task list (like task Queue). In that table you could put the informations about what kind of task should be run, what user created the task, what are the task parameters,  is the task finished or not, error description etc...
On the other side you can create and schedule a Job that would call a stored procedure (with sa credentials) that would go through all unfinished tasks, test if user has permission to run the task and call xp_cmdShell with task parameters. I'd suggest putting as few info as possible into the parameters because of security - for example in your case if all is fixed you do not need parameters at all or you can put only the name of output file as a parameter. After successfull/unsuccessfull run update your task table with success or error data.
Client would only need to insert a row into the task table and wait for job to finish to see the results.
0
 
LVL 7

Assisted Solution

by:Chrisedebo
Chrisedebo earned 100 total points
ID: 21812013
Apologies, yes it is....

However, you are correct, you could hardcode the sysadmin user details in the vb.net app. But that would mean you'd need to re-compile if you changed the sysadmin password. Also, if someone is able to dissasemble your .net app they might be able to get the password out of it.

I think orcic has the best idea.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

695 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