Sorry but I must start with a disclaimer: we're talking about security and division of responsibility, i.e. architecture, so one is not necessarily better or worse. Further to that, these are general suggestions. These solutions may not be conducive in your environment, depending on logistics, security scheme, and many other variables I am not privy to.

BULK INSERT and xp_cmdshell are powerful tools but they arrive with lots of baggage in the area of security as you have learned. Many people simply blast through the roadblocks and start adding people to the sysadmin Role or setting up the proxy and granting exec on xp_cmdshell without understanding the ramifications of doing something like that, so kudos to you for doing better.

---

For backend processing, i.e. where user-interaction is not required, the tools can be quite useful. But, when wanting to expose their functionality to interactive users it can get dicey.

Alternative 1

Create a PowerShell script that:

- leverages the .NET SqlClient library to execute stored procedures as needed

- in place of BULK INSERT use bcp to load flat files into database tables

- in place of xp_cmdshell since we're in a PowerShell context you can simply execute what is needed using PowerShell native commands or the same commands/executables you were calling using xp_cmdshell

Excerpt from the Permissions section in bcp Utility (SQL Server 2005):

Your users can then execute the PowerShell script.

Alternative 2

Create an SSIS package that:

- uses the Execute SQL Task to execute stored procedures as needed

- in place of BULK INSERT use a Data Flow Task to load flat files into database tables

- in place of xp_cmdshell use the Execute Process Task to execute cmd-shell commands or other executables

Your users can then execute the SSIS package directly using dtexec.exe.

Alternative 3

Setup an asynchronous Service Broker (SB) queue that your users can insert a 'request to execute said proc' into. The worker process that services that queue would then asynchronously execute your proc in a different security context, one with the permissions necessary to run BULK INSERT and xp_cmdshell.

This option is not interactive, i.e. the caller would receive confirmation when inserting into the SB queue that a message was added to the queue but they would not necessarily know the outcome of the SB worker process unless they checked back later.

--

In closing, signing a proc that uses BULK INSERT and xp_cmdshell so an interactive user can successfully exec the proc while still maintaining a very limited set of database permissions has merits and demerits. The same applies to the options I show above.