I'm working with SQL Server 2016 and I need to let an user from
MyDatabase to execute
xp_cmdshell. I do the following:
USE [MyDatabase] GO GRANT EXECUTE ON xp_cmdshell TO myUser GO
myUser is an user from
MyDatabase and when I run the above code, logged as
sa, I get the following error:
Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.
Do I need to add the user to
master database? I don't think it is safe.
I need this because I want to write a text file from a stored procedure. I have found the following article, http://www.nigelrivett.net/WriteTextFile.html, saying that I can write a text file using:
You will need to create the data to be output as in dynamic sql statements The first command here creates or overwrites the file - the rest append to the file. exec master..xp_cmdshell 'echo hello > c:\file.txt' exec master..xp_cmdshell 'echo appended data >> c:\file.txt' exec master..xp_cmdshell 'echo more data >> c:\file.txt'
I ask this because my stored procedure will generate a large string (nvarchar(max)) json and when I try to write it down with C# I don't get anything: neither the file or an exception. I've thought to write the json string inside the stored procedure (if I can).
The stored procedure ends correctly, I have checked in the debugger that I get something into the output parameter json (I know it because Visual Studio sets that there is not enough memory to show its contents), the
File.WriteAllText is run because I have a breakpoint on it, and also, the path is absolute. But maybe, there is something that I ignore and I have to double check everything again.