I have a s/p which runs bcp and creates a CSV export file. Works fine in development (my rights) but not for any other user. To run bcp, I need to use sp_configure to turn on 'configure advanced options', then xp_cmdshell to run bcp. Both of these options are OFF by default.
SO: I turn on the advanced options, turn on xm_cdmshell, run my code, turn off xp_cmdshell, then turn off advanced options.
It works great for me but other users get permissions errors trying to run the system stored procs. They can run the s/p in my local db, but not the ones that live in master db. I've been playing with GRANT and EXECUTE AS but have no luck yet with either one. Has anyone ever done this?
Here's my [edited] code, a few things left out for clarity:
ALTER PROCEDURE [dbo].[aMyAppCreateUploadCSV]
DECLARE @SQL varchar(255)
DECLARE @FileName varchar(50)
DECLARE @DestFolder varchar(100)
-- Turn on Advanced security options
EXECUTE master.dbo.sp_configure 'show advanced options', 1
-- make sure xp_cmdshell is turned on and reconfigure with new value
EXEC sp_configure 'xp_cmdshell', 1
SELECT @FileName = 'Upload_TEST.csv';
SELECT @DestFolder = '\\myserver\user\UploadFiles\';
SET @SQL = 'bcp "exec [MyDB].[dbo].aExtractDataCSV" queryout "' + @destFolder + @fileName + '" -t \, -T -c'
EXECUTE master.dbo.xp_cmdshell @SQL
-- turn off xp_cmdshell and reconfigure with new value
EXEC sp_configure 'xp_cmdshell', 0
-- Turn off Advanced security options
EXECUTE master.dbo.sp_configure 'show advanced options', 0
"No pressure, no diamonds.
" - Thomas Carlyle