DECLARE @cmd varchar(1000)
SET @cmd = 'copy \\server\c$\folder\file.txt \\sameserver\newfolder\'
PRINT @cmd
EXEC xp_cmdshell @cmd
My expectation is that when the exec runs it will use the ##xp_cmdshell_proxy_account## which has my network credentials stored. My network credentials are listed as an admin on the server - so there should be no problem and I granted execute permission on xp_cmdshell to my network user account. But, when I simply run the command in Management Studio logged in as the SQL Server login, I get the error:
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
The overall goal is for a .net application to kick off a stored procedure which writes data from the database to a file and then copies the file to a web server. The stored procedure then fires a web service which sends the user a link to the file. I had this all working on an older SQL Server machine but now cannot replicate it on our new machine. I seem to be stuck and pulling my hair out. UGH!
Thanks for the help!!!