FMabey
asked on
Running xp_cmdshell as a non-admin
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_copr od out \\SQLSVR1\c$\Co-Products.c sv -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
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_copr
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
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
https://www.experts-exchange.com/questions/22118390/Proxy-account-to-run-XP-cmdshell.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
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
https://www.experts-exchange.com/questions/22118390/Proxy-account-to-run-XP-cmdshell.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
ASKER
Chrisedebo,
Isn't the link you sent me giving alternatives for SQL 2005?
Isn't the link you sent me giving alternatives for SQL 2005?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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