2

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.

  • 1
    Simply adding a user to master isn't unsafe since the user will have only the public permissions they already had via the guest user. However, granting execute permissions on xp_cmdshell is a security concern because the non-sysadmin user can then execute any ad-hoc OS command on the database server, limited only by the xp_cmdshell proxy account permissions. I suggest you specify why you need to do this in your question as there may be a more secure solution. – Dan Guzman Mar 20 '18 at 11:16
  • @DanGuzman I have added more details about what I'm trying to do. – VansFannel Mar 20 '18 at 11:24
  • 2
    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 proc and can't execute ad-hoc xp_cmdshell commands. See this for an example, except in this case do not add the certificate login to the sysadmin role. – Dan Guzman Mar 20 '18 at 11:48
  • +1 for Dan Guzman. It's exactly what I would do. Doing it in a proc and giving the right to the user to execute the stored proc is far more secure! – Danielle Paquette-Harvey Mar 20 '18 at 12:10
  • Why does this have to be a stored procedure? Why can't you generate your output from Powershell, which can easily write to the file system without any of this hokey xp_cmdshell risk. In fact, you could create the procedure that produces the output, and have Powershell simply call the procedure and write to a file. Use the right tool for the job. – Aaron Bertrand Mar 20 '18 at 13:41
5

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
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.