Grant user from my database to execute xp_cmdshell

Question

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.

Answer 1

I suggest you wrap the code that executes xp_cmdshell in a stored proc in your user database and sign it with a certificate that has the needed permissions. That way, users are limited to the user stored procedure and can't execute ad-hoc xp_cmdshell commands. OS permissions are also limited to the proxy account for non-sysadmin role members. This certificate technique is detailed in Erland Sommarskog's Packaging Permissions in Stored Procedures article.

Below is an example script.

-- Enable xp_cmdshell and create proxy account
USE master;
EXEC sp_configure 'show',1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE; 
EXEC dbo.sp_xp_cmdshell_proxy_account 'YourDomain\YourProxyAccount', 'YourPr0xy@accountPassw0rd';
GO

-- Create certificate in master.
CREATE CERTIFICATE xp_cmdshell_cert
   ENCRYPTION BY PASSWORD = 'All you need is love'
   WITH SUBJECT = 'For xp_cmdshell privileges',
   START_DATE = '20020101', EXPIRY_DATE = '20300101';
GO

-- Create a login for the certificate.
CREATE LOGIN xp_cmdshell_cert_login FROM CERTIFICATE xp_cmdshell_cert;
CREATE USER xp_cmdshell_cert_login;
GRANT EXECUTE ON dbo.xp_cmdshell TO xp_cmdshell_cert_login;
GO

-- Copy cert to user database
DECLARE @cert_id int = cert_id('xp_cmdshell_cert')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'All you need is love',
              'All you need is love')

SELECT @cert_id, @public_key, @private_key

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE xp_cmdshell_cert
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''All you need is love'',
          ENCRYPTION BY PASSWORD = ''All you need is love'')'

EXEC YourUserDatabase.sys.sp_executesql @sql;

ALTER CERTIFICATE xp_cmdshell_cert REMOVE PRIVATE KEY;
GO

USE YourUserDatabase;
GO

CREATE PROC dbo.CreateFile
AS
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';
GO
ADD SIGNATURE TO dbo.CreateFile BY CERTIFICATE xp_cmdshell_cert
   WITH PASSWORD = 'All you need is love';
GRANT EXEC ON dbo.CreateFile TO YourUserOrRole;
GO
ALTER CERTIFICATE xp_cmdshell_cert REMOVE PRIVATE KEY;
GO

Answer 2

With regards to the aspect of the question dealing with permissions related to executing xp_cmdshell, I agree with (and upvoted) Dan's answer. However, given the new information regarding the reason for this request (i.e. writing out large JSON documents), security for xp_cmdshell becomes a moot point since xp_cmdshell can only handle a maximum of 8000 VARCHAR characters (less if using a Double-Byte Character Set and certain characters therein, but that's probably not the case here, just something to be aware of). Also, I seem to recall that certain characters did not work so well on the command line and couldn't easily be escaped (but currently can't find any).

So perhaps it would be best to accept Dan's answer for this question (since it is correct for the question as stated), and then open a new question about how to best export large JSON documents, and I can transfer this answer over to that question.

---

This operation can be handled rather easily via SQLCLR using the File class.

Please see the following answer of mine, also here on DBA.SE, that includes some code examples for doing this type of thing:

Exporting results to XML

Also, for anyone not wanting to mess with the coding, deployment, etc: there is a File_Write function available in the SQL# library (which I wrote) that handles this and even allows for passing in the desired encoding so that the file can be saved as UTF-8 with a Byte Order Mark (BOM), for example. Please note that this function is only available in the Full version, not in the Free version. This is a fairly simple function to create, but best to have options.