I know I'm a few days late to the party on xp_cmdshell, but last week was ISO-27001 audit week and I was a bit busy. 😉
You can add me to the list of people who understand that xp_cmdshell is not the worst practice it's been made out to be. Disabling it does nothing to "keep the bogey man" out of your database. I guess disabling it could make people feel better, but it's a false sense of security.
Can it be used do damage the server? Well, duh - of course. But look at what's required to use it:
1. Only people with sysadmin privs (or control server, which I don't grant) can use it.
2. Only people with sysadmin privs can turn it on.
3. Hence, it doesn't matter if it's turned on or off because at attacker with sysadmin privs will simply turn it on.
It's as simple as that. The sa login has been around a very long time and is the well-known favorite of attackers. I once got to see a demo of hacking it and it was scary just how easy it was using automated tools. The most effective solution is to disable it and leave it disabled. Not having SQL logins with elevated privs enabled precludes the argument around enabling xp_cmdshell by addressing the real issue of security configruation.
I've heard the argument of renaming it and creating a honeypot sa login, but I don't even go there because it gives the attack vector somewhere to go.
Jeff made the point earlier about not giving people explicit privs to run it. I take it a step further and don't give people permission to run xp_ anything.
Joe, I understand your point about poorly-designed third party apps flipping out if they aren't running with sysadmin privs. I know that's a real problem for some apps. If it were up to me, I wouldn't install something like that so we never develop a dependency on it. If it's required, I would isolate the database for the application to it's own instance. Of course, that's only if I can't get along without the app to begin with.
Getting back to the larger issue of security, an attacker who has sysadmin privs doesn't need xp_cmdshell to do damage. Think about what the login has privs to do - anything the attacker wants. This includes querying all data from all tables in all databases. Such a breach is a complete disaster. The only thing left is to read about it on the news and pay the heavy costs associated with it.