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
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