Your Name Here (4/1/2013)
I apologize for being late to the party. Typically I'd quietly read but I'd like to post my thoughts even though I'm not at the level of prior contributors to this thread.Everyone has valid points and of course it all boils down to the environment you're in. In one extreme, xp_cmdshell isn't so bad while in others it's just a horrible idea. Regardless, you have to decide if you trust the people that can execute this AND trust the code being executed. If a sysadmin "tests" code in production, the deserve what they get up to and including getting fired.
I use xp_cmdshell only for my own "admin" functionality but I [do] try to force the little bit of auditing I'm able to: it's off by default and my jobs (precious few that need xp_cmdshell) explicitly turn it on, do their thing then turn it off again (error on "off" = alert) along with logging the exact statement executed. The errorlog contains the enable and disable. Poor man's auditing? Sure. But it's the best I can do with the tools I have.
I give props to any DBA that has the time to learn .NET. I'd love to but if it's not T-SQL or PowerShell, the answer is "no". The sad reality is I'm too busy being a DBA to have time to be a developer. When Microsoft decides to provide a different method of helping me accomplish the things I need to - by exposing the operating system or increased functionality in their current commands - I'll start using them instead, but I just can't "roll my own" because I don't like what Microsoft gives me.
Some solutions may be easy (MS: please add a 'total drive space' column to xp_fixeddrives) and some may not. If only Microsoft would take a look at what the average DBA uses xp_cmdshell for and provide a better alternative, life would be grand (gee MS, if we need auditing, why not bake it into xp_cmdshell...?)
BTW: Whether you're "for" or "against" making this functionality available, the commentary and passion for the topic show the level of skill, experience and professionalism (agreeing to disagree) of the SQLServerCentral members. The DBAs we really need to be aware of (read: "afraid of") are the ones who read Steve's editorial and said "Meh. Who cares?"
Great post and thanks for taking the time.
Still, the points being made about whether to use xp_CmdShell or not are only secondary to me. If someone doesn't actually want to use the tool, I get that. The real problem is that even some of the very heavy hitters in the world of SQL Server imply or even come right out and say that turning it off provides additional security. My take is that the level of security provided by turning it off is so thin and weak that you might as well leave it turned on for all the good that turning it off will actually do for you.
I would take the same stance even if I agreed that you should never use xp_CmdShell.
--Jeff Moden
Change is inevitable... Change for the better is not.