SQL Server – Grant User Permission to Execute xp_cmdshell

permissionssql server

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.

Best Answer

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
Related Question