We have a program that uses a custom stored procedure to import data from a flat file. It uses xp_cmdshell and the bcp utility. The software has recently been upgraded and we are doing testing on this process and had to set up the proxy on the new SQL server so users can run this procedure. The account used is not a domain or local administrator. Still I would prefer not to use this method so I wanted to use a job and CmedExec. Below is my command string. The problem is I am not sure how to pass the variables into the job.
select @cmdstr= 'bcp '+db_name()+'..stored procedure in '+ @DataPath + @FileName + ' -S'+@@ServerName+' -UUsername -Ppassword -f '+@FMTFILE EXEC @retval=master..xp_cmdshell @cmdstr
The filename will change each time it is run by the user and the location of the format file and server could change so I don't want to hard code anything into the statement.
Is this possible or is my best option to use a secure xp_cmdshell proxy, if there is such a thing.