February 22, 2010 at 11:59 am
Greetings all. According to BOL on xp_cmdshell...
...
Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
...
However, there is not an example of how to grant execute permissions to users, so I tried...
GRANT EXEC
ON xp_cmdshell
TO [SCS Reports]
and got...
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name 'xp_cmdshell'.
Then I realized I had to use master, and got...
Server: Msg 4604, Level 16, State 1, Line 1
There is no such user or group 'SCS Reports'.
My question is, in lieu of giving users access to the master database or adding them to the sysadmin role, how can I grant permissions to execute xp_cmdshell from another database?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 22, 2010 at 12:28 pm
as you know usage of xp_cmdshell is to be avoided.
We don't use xp_cmdshell out of sysadmin context, but I believe you can grant it by altering settings for sqlagent.
SQLAgent properties Jobsystem has a setting "restrict xp_cmdshell to sysadmins". This setting is checked by default. If you uncheck it, others can use xp_cmdshell.
Once again ... if you can, avoid opening xp_cmdshell by any means !
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
February 22, 2010 at 12:57 pm
Yep, after reading more about it, I realize now why it is not for users, as it could be dangerous. My problem is that there is a need to save an excel file to a folder, upload the data, then move the file to an archive folder. Simple enough, and I have many procedures like this that I run myself using QA. I suggested scheduling a job to do it on a regular interval, but that did not go over well. Is there another option to move a file from one folder to another, that could be controlled by the user, and would not be a security risk?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 23, 2010 at 2:08 am
Greg Snidow (2/22/2010)
Yep, after reading more about it, I realize now why it is not for users, as it could be dangerous. My problem is that there is a need to save an excel file to a folder, upload the data, then move the file to an archive folder. Simple enough, and I have many procedures like this that I run myself using QA. I suggested scheduling a job to do it on a regular interval, but that did not go over well. Is there another option to move a file from one folder to another, that could be controlled by the user, and would not be a security risk?
You could propose to have the job activate by an application request. "Push the button and we'll process your xls'
How would you do that ?
To keep activation control, IMO it is best to use an alert .
Have a look at my little script "Help to tighten the use of xp_cmdshell ..." at http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
It has been written for use on SQL7 and SQL2000, but it also works on the more recent versions of sqlserver.
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
February 24, 2010 at 3:26 pm
Thanks for the tip, and that will take me some time to understand. In the mean time, I successfully convinced the user that it will be sufficient for me to schedule a job to run twice per day, and the problem went away for now.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 25, 2010 at 12:00 am
Once again it is been prooven : a sound dba must pop the questions !
and the attitude will change from [b]"must now ... chop chop.. cannot do it other way..." [/b] to " well ... if you can deliver it a couple of times a day, it's OK too"
Great job !
😎
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply