As a proof of concept we're trying to insert an xp_cmdshell command into an existing solution. Currently an application invokes a stored procedure on our database server which when profiled looks like:
declare @P1 int
set @P1=1
exec Name_Of_The_SP @param1 = 3, @param2 = 'blah', @parametc = 'blahetc', @ID = P1 output
select @P1
The SP essentially opens a transaction, inserts a row, and then commits. Inside this we added:
exec master..xp_cmdshell 'dir > c:\test.txt'
When we then run the first block of code in a SSMS query window the file is generated on the server as expected. But when we use the application to invoke it then the rows are inserted as normal but the file isn't generated?
The SQL Server and SQLAgent users are local admins and sysadmins so can't see any issues there. Tried making the application user a local admin also, to no avail, it was already a sysadmin.
This is SQL Server 2000
We managed to figure this out - we (I) were overlooking in profiler that the exec was coming in under a different login. Granting execute permission to master.dbo.xp_cmdshell specifically got it working. Apologies to anyone who spent any time/effort on this!