Jeff Moden (11/21/2013)
opc.three (11/20/2013)
And the sysadmin members? ...oh that's right, you argue that you must trust them implicitly.
Jeez, Orlando. If you can't trust an SQL Server Admin or a Windows Admin to do the job you hired them for, then they shouldn't be sysadmin members or you need to fire them for being untrustworthy, etc., because they can't do their job right if they can't be trusted. It's that simple. Sure, you can setup some really strict auditing but who's going to audit the person who sets that up and monitors the logs?
You have to trust someone or it's time to turn off the computers and go home.
Not quite. This is why network security, database admin and system admin are separate groups within a large organization. Like I said, for you your approach works and in most shops "xp_cmdshell is safe" is true enough, but irresponsible to simply bandy about.
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.
Correct. In comparison to the number of apps that are written in such a thoughtful and security-wise fashion, what percentage of apps have you seen that are written incorrectly? Like I said, that's a real concern.
Absolutely agree. It is a real shortcoming in many app designs. From personal experience and reading the experiences of others I suspect the security in most apps that interact with SQL Server are poorly thought our or poorly implemented when it comes to database security and auditing. From my perspective, the introduction of xp_cmdshell into any stack immediately brings the app into that poorly thought out or poorly implemented category due to its security and auditing shortcomings.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato