Turning xp_CmdShell off is a useless and futile thing to do. Look at what Chuck.Hamilton wrote above. If an internal or external hacker gets in as "SA", they can get to the command prompt in a totally undectable fashion even if xxp_CmdShell is turned off.

My stance on xp_CmdShell is that it’s no longer a security hole. Rather, malicious use of xp_CmdShell is a symptom of improper security. I’ll also state that turning it off in the face of improper security is a bit like serving drinks at a party to celebrate sobriety.

Consider the following, please.

Who can use xp_CmdShell directly? The answer, of course, is only those people or applications that have “SA” privs or those people that you’ve made the mistake of granting proxy privs to. As Steve suggested, let’s assume that you only allow no “SA” people/apps to execute xp_CmdShell through injection proof (both SQL Injection and DOS Injection) stored procedures and haven’t made the mistake of granting direct execution proxy privs.

Now consider a system where only trusted DBA’s have privs. Is there a benefit to turning xp_CmdShell off? A lot of people insist the answer is “Yes” especially since turning it on is a “logged event”. Have you ever look at what gets logged when you turn it on? On the occurrence of the event is logged in the SQL logs and the default trace will show the machine name and the user that logged in. Nothing is logged about how it was used. Further, a clever person with “SA” privs could create an alternate login, use xp_CmdShell from a different machine, and delete the login when done to cover tracks.

In the case above, turning xp_CmdShell did nothing to prevent the loss of or theft of data. You might not even be able to identify who did it.

Let’s discuss another useless reason for turning xp_CmdShell off even on an unsecure system. Yes, yes, turning it off will keep most honest people away but does it actually enhance security?

The answer is “No”. The system must be secure to prevent a malicious insider or external attacker from simply turning it on. If either or the two types of people I just mentioned have “SA” privs, they don’t even need to use xp_CmdShell to get to the Command Line. They can very simply and quickly create a self-deleting scheduled job to execution “Operating System (Command Line)” tasks and NOTHING get’s logged.

My stance is that, in the face of poor security, turning xp_CmdShell off is like putting a band-aid on a stab wound. People only think that it adds some sort of security and because it may lure some folks into a false sense of security, may actually make them not consider the other incredibly important aspects of proper security.

What IS proper security? The first and most important step is easy. No one and no thing (apps) other than DBAs and system jobs should ever have more than “DBO” privs. If a person or thing doesn’t have “SA” privs, it can’t use xp_CmdShell except through properly configured and constrained stored procedures. If anything else is true, then whomever or whatever has the “SA” privs can get to the Command Line using many different methods and some are absolutely undetectable. Logs created by the methods that do some logging are nothing more than written testimony that the security of the system sucks and that the DBAs didn’t actually do their primary job, that of security.

Considering all of the wonderful things that a DBA (I use xp_CmdShell to {for example} call PowerShell to create system/domain wide disk usage reports) or stored procedure (make some absolutely awesome ETL systems without having to setup SSIS, which is another system that would need to be properly secured) can do and that turning xp_Cmdshell off is a thin veil over rotting meat in the face of poor security, I say that it should never be turned off. Rather, proper security should be implemented, which is what you’re supposed to do, anyway!

{EDIT} What about 3rd party apps that have "SA" privs? First, and I don't use this word a lot, but that's just plain stupid. If the powers that be insist upon it, then put that app on a separate server because that app is a major security hole. You also need to make sure that the SQL Service and Agent on that separate machine have virtually no privs beccause, like I said, anyone or any thing that has "SA" privs can get to the Command Line with the same privs as the SQL Service or Agent in a totally undectable manner whether xp_CmdShell is turned on or off. THAT's the security hole. Not xp_CmdShell.