0

Imagine the following scenario: I need to execute a stored procedure which, amongst other things, needs to EXECUTE xp_cmdshell to run a command-line script which executes a bulk insert (bcp) command to put data into another database.

I have three Windows user accounts on the server: MyAdmin, MyProxy and MyUser.

MyAdmin is db_owner of the database and has bulkadmin server role. MyProxy is the account associated with xp_cmdshell via sp_xpcmdshell_proxy_account. MyUser has EXECUTE permission on the stored procedure and nothing else.

If I login as SERVER\MyUser and execute the stored procedure, it fails with error "The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

So I thought the answer was to grant EXECUTE on xp_cmdshell to MyProxy, but this didn't work either. I then granted EXECUTE on xp_cmdshell to MyAdmin and changed the definition of the stored procedure to WITH EXECUTE AS 'MyAdmin' and although xp_cmdshell is executed OK, the bulk insert fails.

What must I do to make this scenario work?

Update: was asked on StackOverflow.com (1440332), but got no answers - hoping for better result here

Jazza
  • 135
  • 6
  • Is this an on-demand type of operation or scheduled? – Sam Sep 24 '09 at 22:09
  • what is the error you get when the bcp fails? – SQLRockstar Sep 26 '09 at 13:59
  • Sam: ultimately, it will be run as a scheduled task via SQLAgent/SSIS. SQLRockstar: error msg is "you need bulk insert permissions to execute bulk copy operations" or something to that effect – Jazza Sep 26 '09 at 20:14

2 Answers2

0

I seem to remember a BCP step in SSIS. Could you create a SSIS package and run that? You can run SSIS packages from .net.

Sam
  • 1,990
  • 1
  • 14
  • 21
  • Interestingly, I do have a potential future requirement to create an SSIS package which amongst other things, will execute this procedure, so this is a definite possibility - thanks! – Jazza Oct 02 '09 at 21:11
0

I would have made this a comment instead of an 'answer' had I permissions to do so, but, why not use the BULK INSERT T-SQL command instead of xp_cmdshell to bcp?

Tim Benninghoff
  • 51
  • 2
  • 4
  • The stored procedure can execute any command-line executable file, which is done via xp_cmdshell. Executing bcp commands is one of many tasks the procedure can perform. – Jazza Oct 02 '09 at 21:09