how to grant permission for xp_cmdshell

Question

how to grant permission for xp_cmdshell

Richard Cranston

Hall of Fame

Points: 3680
More actions
April 21, 2004 at 12:27 pm

#82564

I get an error not authorized to xp_cmdshell when I run the following:
DECLARE @bcp as varchar(8000)
select @bcp = 'bcp "Select * From Salesperson_Dimension" queryout C:\SalesPersonFile.txt -T -c'
exec master.dbo.xp_cmdshell @bcp
Can anyone provide a step by step on how to grant/revoke execute permission for xp_cmdshell? Either EM or T-SQL?

Viewing 12 posts - 1 through 11 (of 11 total)

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

pilam152 Old Hand Points: 354 More actions · Answer 1

I had the same problem before. You have to give the username the server role of Server Administrator. The SQL Server requires this permission to allow the xp_CMDShell to execute

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 2

I did assign the User the role of Server Administrator, but our vendor software application received errors afterwards; as that was the only recent change made. After removing the role, the application ran okay.

So either I can grant just permission for the procedure or should I create a new User with the required role.

pilam152 Old Hand Points: 354 More actions · Answer 3

We set up a new User for our Bulk Copy Application, gave the user Read/Write Permissions on the Database and a Server Role of Server Administrator, and it worked. You also have to set the Default Database to the one that you are doing the BCP on.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

But doesn't giving the user the SA Server role give them the ability to change their own permissions? Doesn't this create a huge security hole?

pilam152 Old Hand Points: 354 More actions · Answer 5

I think that the concept to shell out to the NT operating system already offers a huge security hole. I did find some information on a microsoft site about setting up DB users that are not System Administrators, but can run xp_CmdShell. In the section under remarks there is some useful information. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 6

The xp_cmdshell extended stored procedure is in the master database (as are all extended stored procedures). If you want to give access to xp_cmdshell without giving out sysadmin role rights, you can do any of the following (all are assumed to execute against the master database):

Grant Execute rights to the public role:
GRANT EXECUTE ON xp_cmdshell TO public
Grant Execute rights to the guest account:
GRANT EXECUTE on xp_cmdshell TO guest
Give a user access to the master database and then grant that user execute rights:
EXEC sp_grantdbaccess 'MyUser'
GRANT EXECUTE ON xp_cmdshell TO MyUser
Give a user access to the master database, create a role, put the user in the role, and then grant that role execute rights:
EXEC sp_grantdbaccess 'MyUser'
EXEC sp_addrole 'CommandShellUsers'
EXEC sp_addrolemember 'CommandShellUsers', 'MyUser'
GRANT EXECUTE ON xp_cmdshell TO CommandShellUsers

Options 1 and 2 are pretty much identical. The guest account is enabled for the master database (this is required) and by rule all users are members of the public role, to include guest. That means you don't have to grant a user specific access to the master database if you don't want to as in options 3 and 4. However, options 3 and 4 allow you to restrict who would actually be able to call xp_cmdshell. Any of the options keep you from having to grant a particular login membership into the sysadmin role.

One more thing: if a login is not a member of the sysadmin role, the proxy account must be set. You can do this with the xp_sqlagent_proxy_account stored procedure. More here:

Books Online: xp_sqlagent_proxy_account

K. Brian Kelley
@kbriankelley

Phillipe Patacca SSC Journeyman Points: 91 More actions · Answer 7

Thanks K. Brian Kelley - you have saved us a truckload of effort in answering this post.

We actually discovered that your suggested solution in item 4 above did not work as expected. However, using xp_sqlagent_proxy_account we did the following:

1. Created a Windows user account - e.g. 'SQLXPShell' - which is a member of the Domain Users group only (this can be changed later if neccessary);

2. Limited the permissions on this account so that it could only do exactly what we wanted it to do (e.g. only create folders in c:\application\folders\);

3. Ran xp_sqlagent_proxy_account to make the 'SQLXPShell' account the proxy account, e.g. -

exec xp_sqlagent_proxy_account N'SET', N'MyDomainName', N'SQLXPShell', N'password'

This way, we are able to exercise a fine level of control over the use of the xp_cmdshell extended stored procedure and exactly what can be done. Of course, it means that the permissions of the user account must be in line with the functionality required by any stored procedure that calls xp_cmdshell. This could be controlled by adding the user to one or more groups etc.

Hope this adds something useful to the body of knowledge.

Cheers!

Phillipe and Marty, MDA

XuJohn Right there with Babe Points: 725 More actions · Answer 8

Hi Phillipe,

I have a similar problem here. However, When I ran that "xp_sqlagent_proxy_account" , I got the error message like this:

"Error executing extended stored procedure: Specified user can not login"

Can you help me out?

Thanks in advance

Phillipe Patacca SSC Journeyman Points: 91 More actions · Answer 9

Are you running the procedure "xp_sqlagent_proxy_account" from ISQL as an administrator?

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 10

how is this working for SQL7.0 sp4 ?

I've seen a howto but cannot find it anymore

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

XuJohn Right there with Babe Points: 725 More actions · Answer 11

No. I want non-admin/sa user to be able to run that procedure. My problem was that I couldn't assign an domain user to that sqlagent proxy account, even I followed SQL BOL.

Thanks