grant EXECUTE to user on XP_CMDSHELL

Question

grant EXECUTE to user on XP_CMDSHELL

beejug1983

SSCertifiable

Points: 6491
More actions
March 12, 2012 at 6:44 pm

#265754

Hi,
I am using SQL Server 2005, I have to give execute permission to one of my user on XP_CMDSHELL. Please let me know step by step how can I configure it.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

I wouldn't grant EXECUTE privs on xp_CmdShell to anyone. Would your user be happy with a stored procedure that did whatever they wanted to do with xp_CmdShell without actually being able to execute xp_CmdShell themselves?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

beejug1983 SSCertifiable Points: 6491 More actions · Answer 2

Hi Jeff, I understand the importance of xp_cmdshell. But it is special request by user which I can not deny. Please let me know how can I give execute permissions on xp_cmdshell to that user.

Thanks

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

beejug1983 (3/13/2012)
But it is special request by user which I can not deny. Please let me know how can I give execute permissions on xp_cmdshell to that user.

Based on that, I'll bet you haven't even asked.

I'm pretty sure I know the way to do this without giving the user SA privs but I'm on my way to work. Hope you can wait until I get home because it does require a bit of detail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

beejug1983 SSCertifiable Points: 6491 More actions · Answer 4

Sure Jeff, I am eagerly waiting for your reply.

Thanks

EdVassie SSC Guru Points: 60472 More actions · Answer 5

You need to set up a Proxy account in order for someone without SQL Sysadmin privileges to use xp_cmdshell. BOL can tell you where this is configured.

All commands run by all non-sysadmins via xp_cmdshell will use the rights given to the proxy account. Typically this will mean the proxy account has very limited access to the end-users personal folders.

The password for the proxy account is encrypted and stored within SQL Server, so if you change the password in Windows you also need to change it in SQL Server.

I completely agree with Grant that giving access to xp_cmdshell to non-sysadmin people is a bad idea. You are poking a hole in your security system and allowing those people unrestricted access to do what they want.

My recommendation is to talk to your manager and explain the risks. If it is a senior manager who has requested this access, also explain the risks to that person. Explain that you can set up a controlled interface to xp_cmdshell that will execute specific pre-defined commands in stored procedures that will eliminate these risks.

Managers like to get their way, but they also want to avoid risks. Your job definitely includes the responsibility to tell them is something is risky. If you do not tell them it is risky, they will not know but will blame you when something goes wrong. If you tell them the risks and they still want to go ahead, confirm in writing what you have been asked to do and hopefully if the s hits the f it will be blown away from you.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Apologies for being MIA. Work has been a bit lengthy lately.

For my example code (which follows), the first thing you need to do is to setup a Windows user called "TestDummy" with just normal user privs. You also need to setup a user called SqlCmdUser and give it privs to whatever drives you want someone with the ability to run xp_CmdShell to be able to see.

Now, I create a test database in the following code. Everywhere it says "yourdomainname", you'll need to replace with the domain name of your Windows server.

You need to be REAL careful with this code. This is code I did a demo with to prove that xp_CmdShell could actually be used safely and I wanted to make it easy to rerun. If you don't review the DROPs in this code before you run it, you're crazy.

You must also have enabled xp_CmdShell before you do any of this. You can do that with the following code which comes straight out of Books Online.

[font="Arial Black"]WARNING!!! THIS CODE TURNS ON XP_CMDSHELL. HAVING IT TURNED OFF WON'T ACTUALLY HELP YOU IF YOU HAVE ANY PUBLIC FACING OR UNTRUSTWORTHY INTERIOR USERS THAT HAVE "SA" PRIVS!!! INSTEAD OF MESSING AROUND WITH THIS CODE, YOU SHOULD BE FIXING YOUR SECURITY, INSTEAD!!! 😉[/font]

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

Here's my code. It does everything else. Again, REVIEW THE DROPs before you run it. Replace "yourdomainname" with the domain name of your, well, your domain. 😉

The code does a bunch of one-time setup to build the correct users. There's some display code in there to show that the only privs the TestDummy user has is "PUBLIC" with EXECUTE privs on a sample stored proc that does a simple "DIR" command. You can, of course, to change the stored procedure to take a full DOS command but I still recommend that you limit the user to just those things the (s)he needs to do.

The code also does a demonstration that shows that it can execute the stored procedure that contains a call to xp_CmdShell but can't execute xp_CmdShell itself. As usual, for more information, see the comments in the code and Books Online.

--===== Make sure none of the test objects I use exist ahead of time so that we can see that this all actually works

SELECT '****************************** Making sure the things we need don''t already exist. ******************************';

USE MASTER;

DROP DATABASE MyTester; --BE REAL CAREFUL HERE!!! Drops the database I tested against

EXEC sp_xp_cmdshell_proxy_account NULL; --Drops the cmd shell proxy just to be sure.

DROP USER [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

DROP LOGIN [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

DROP USER [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.

DROP LOGIN [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Recreate my test database and the user which only has "public" privs.

-- I believe the DEFAULT_SCHEMA is important here.

SELECT '****************************** Creating [MyTester] DB and TestDummy. ******************************';

CREATE DATABASE [MyTester];

GO

USE [MyTester];

CREATE LOGIN [yourdomainname\TestDummy] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTester], DEFAULT_LANGUAGE=[us_english];

CREATE USER [yourdomainname\TestDummy] FOR LOGIN [yourdomainname\TestDummy] --This just maps the database for the user

GO

--===== This just displays how limited the TestDummy user is

EXEC sp_helpuser [yourdomainname\TestDummy];

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Now we build the Login and proxy account using the SqlCmdUser I built in Windows on my box at home.

-- IMPORTANT!!! A step we cannot skip is that we have to build a user from the SqlCmdUser login.

-- NOTE THAT THIS MUST BE A SINGLE USER AND NOT A WINDOWS GROUP!

SELECT '****************************** Building/Granting Proxy user stuff. ******************************';

USE [master];

CREATE LOGIN [yourdomainname\SqlCmdUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

CREATE USER [yourdomainname\SqlCmdUser] FOR LOGIN [yourdomainname\SqlCmdUser] WITH DEFAULT_SCHEMA=[dbo];

EXEC sp_xp_cmdshell_proxy_account 'yourdomainname\SqlCmdUser','SqlCmdUser';

--===== Very important here... we have to grant access to xp_CmdShell to the new Window's user...

GRANT EXECUTE ON xp_CmdShell to [yourdomainname\SqlCmdUser];

GO

--===== This just displays how limited even the SqlCmdUser is!!!!

EXEC sp_helpuser [yourdomainname\SqlCmdUser];

GO

-----------------------------------------------------------------------------------------------------------------------

-- ********** NOTE THAT EVERYTHING ABOVE IS AS WE HAD IT BEFORE! **********

-- ********** NOTE THAT THE ONLY THING WE HAVE TO DO IN THE STORED PROCS (SEE BELOW

-- ********** IS TO INCLUDE "WITH EXECUTE AS OWNER"

-- heh... And Bob's your Uncle!

-----------------------------------------------------------------------------------------------------------------------

--===== Create a stored procedure in the new "MyTester" database that uses xp_CmdShell.

-- Keep in mind that, right now, we're signed in as a member of "dbo".

USE [MyTester];

GO

DROP PROCEDURE dbo.GetDirInfo;

GO

CREATE PROCEDURE dbo.GetDirInfo

WITH EXECUTE AS OWNER

AS

EXEC xp_cmdshell 'DIR C:\';

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

;

GO

--===== Give the general puplic privs to run the sproc.

GRANT EXECUTE ON dbo.GetDirInfo TO PUBLIC

;

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Now, show that the "TestDummy" user can execute the proc but not xp_cmdshell itself.

-- Simulate logging in as a user with low privs...

EXECUTE AS LOGIN = 'yourdomainname\TestDummy'

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

-- This works... (which is what we want)

PRINT REPLICATE('=',80);

PRINT '********** Testing execution of dbo.GetDirInfo **********'

EXEC dbo.GetDirInfo

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

-- This doesn't... (which is also what we want)

PRINT REPLICATE('=',80);

PRINT '********** Testing execution of xp_CmdShell directly **********'

EXEC xp_cmdshell 'DIR C:\'

;

GO

--===== Test complete... go back to normal.

REVERT

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lempster SSCoach Points: 15651 More actions · Answer 7

Jeff,

Does your solution work for users that do not have a direct login, i.e. they have access to the instance via Windows Group membership? A frustrating afternoon of trying to get it to work leads me to think "no".

A solution I have used is to create a stored procedure that creates a self-deleting SQL Server Agent job and passes the command to be executed by xp_cmdshell into the @command parameter of the msdb..sp_add_job_step proc. I can then disable the xp_cmdshell feature via sp_configure.

Regards

Lempster