| Author |
Topic |
|
ching1874
Starting Member
11 Posts |
 Posted - 2011-03-17 : 21:33:15
|
| Hi Guys, in SQL 20005, the xp_cmdshell is turned off by default. I knew that I can turn it on by using sp_configure xp_cmdshell. By I had a DB framework that calling dayend EMS job which will turn it off again and thus many of my jobs or store procedures just couldn't work as they would use the xp_cmdshell for processing. Just read through some forums and MSDN, they are suggesting to use the following to grant right to the users. GRANT exec ON xp_cmdshell TO 'Domain/User'So my question is, by using the above statement, would the specific user still have the "xp_cmdshell" enabled even it is turned off? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-17 : 21:35:19
|
| No, if it's disabled via sp_configure no one can run it. |
 |
|
|
ching1874
Starting Member
11 Posts |
Posted - 2011-03-17 : 21:41:48
|
| Thanks for the prompt reply. I will try another way around. To solve the above problem, I managed to add a new job which is to enable the xp_cmdshell after the DB Framework disabled xp_cmdshell. But then I found that the other store procedure required xp_cmdshell turned on could not work -- what I mean couldn't work is ,for example, I had a store procedure which would call webservice for report job processing, it returns with a timeout error. Any thoughts???it seems to me it takes some times for the xp_cmdshell to be enabled.<---this is what I guess only |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-17 : 21:46:36
|
"I had a DB framework that calling dayend EMS job which will turn it off"can you change it not to turn the xp_cmdshell off ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ching1874
Starting Member
11 Posts |
Posted - 2011-03-17 : 21:49:52
|
| The DB framework is designed by the so-called Infrastructure team and they want to standardize the backup job . Life would be much easier if they allow me to change..... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-17 : 22:03:00
|
| If your infrastructure team is using SQL Agent jobs, there's very little, or nothing, they need to use xp_cmdshell for that can't be done in an Operating System command step. There's certainly no reason they'd need to turn xp_cmdshell off if their backup jobs depend on it, unless they're paranoid about security...and they wouldn't be using xp_cmdshell if they were.Sounds like a hack that they don't know how to work around. And other than copying files, there's not much need for xp_cmdshell in a backup job. |
|
|
|
ching1874
Starting Member
11 Posts |
Posted - 2011-03-17 : 22:19:23
|
| I have also no idea why their batch job which copy the db backup to EMS required the xp_cmdshell off. anyway, do you guys have any thoughts on the timeout issue after the xp_cmdshell is enabled again in the very beginning? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-17 : 22:36:14
|
| Are you running RECONFIGURE after turning it on? |
|
|
|
ching1874
Starting Member
11 Posts |
Posted - 2011-03-17 : 22:37:42
|
| Yes, after the EMS job, i have a job running the followingEXEC sp_configure 'xp_cmdshell', 1;GORECONFIGURE;GO |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-17 : 22:53:12
|
| I can't think of anything else. My suggestion is, if you can't modify the infrastructure jobs, look at modifying your own so that they don't need to use xp_cmdshell. Stored procedures using xp_cmdshell to call a webservice to process reports...that sounds a lot more complicated than it needs to be (unless I misread your earlier post). |
|
|
|
|
annoying xp_cmdshell required to turn on