I'm ultimately trying to find a way to allow the safe use of BCP and selected other command-line functionality from within SQL Server.
The organisation I am in currently have xp_cmdshell disabled although I feel if I can present a safe security model for its use they would consider turning it on.
My idea so far is to create a wrapper stored proc that executes command-line operations on behalf of the user. The operation would be passed in by the user as a string which could then be
- checked to ensure it doesn't contain any obviously dodgy stuff - i found a function here that should help with this (http://msdn.microsoft.com/en-us/library/aa175398(v=sql.80).aspx)
- only allow calls to specific programs or operations - in my case the first being BCP
My DBA skills are somewhat limited but here is what I'm hoping from a security context
- all normal user accounts are not allowed to execute xp_cmdshell
- we would create a user (who the sproc would EXECUTE AS) with rights to call xp_cmdshell and not much else, not an administrator in other words
- the users existing permissions would be used to read the data from the table but combined with the permissions of this extra account when using the wrapper sproc.
If you could tell me if I'm on the right lines and also other things I need to consider as this is something I would like to present to the DBAs