Jeff Moden (11/20/2013)
That's just it. There are no security challenges to it. I don't allow users to use xp_CmdShell directly. I don't allow apps to execute it directly (never mind them having SA privs). They have no chance of elevating their privs because they cannot use it that way but, even if they could, they wouldn't get far because I also limit what the SQL Server login and the SQL Server Agent login have privs to do. So far as audit goes, you can bet your sweet bippy that the stored procs that use xp_CmdShell log who called them. Heh... I even do that with some of the stored procs that don't call xp_CmdShell.
And the sysadmin members? ...oh that's right, you argue that you must trust them implicitly.
As for auditing, lets ask the question about how many apps that have insert/update/delete privs that don't pass the identity of the person using the app. Now That's a concern and that includes SSIS.
If you're doing it right then your internal apps are written such that your app users maintain their identity all the way through the stack and in a SQL Server context ORIGINAL_LOGIN() will be your friend. If you're dealing with a public-facing website that allows users to contribute or manage content and you don't have individual database Logins per web-user (which you won't if you want to scale up leveraging conection pooling of any kind) then you have a whole different set of auditing challenges.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato