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

  • 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

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.

  • 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 2 '09 at 21:11

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?

  • 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 2 '09 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.