Can we minimise the risk of using xp_cmdshell?

Using xp_cmdshell can be quite helpful and sometimes possibly the only answer to some scenarios. I've read some posts on the internet that enabling xp_cmdshell might jeopardize the security of the database/server.

My question is that, is there anything we can do to reduce the risk? For example can we set some restrictions/applying users role, etc providing safeguards to mitigate the risk?

Thanks.

Well, you could not grant execute on it explicitly to end users (deny it even), and only enable it in stored procedures that use EXECUTE AS with some login that does have execute permissions. Then grant execute on only that stored procedure to the user that needs to run the command.

First, make sure xp_cmdshell is enabled for the instance:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Now depending on operating system and SQL Server service account, you may need to set up a proxy account (and to do this, thanks to UAC, you may need elevation and launch SSMS as an administrator):

EXEC master..sp_xp_cmdshell_proxy_account 'Domain\User', 'Password';

Then create a wrapper in your database that does whatever you need it to do (this is just a sample of a VERY generic wrapper that doesn't really protect you from anything; only using it to demonstrate):

USE yourdb;
GO
CREATE PROCEDURE dbo.uxp_cmdshell
  @cmd VARCHAR(2048)
WITH EXECUTE AS OWNER
AS
BEGIN
  EXEC master.dbo.xp_cmdshell @cmd;
END
GO

Now give your user or role permissions to execute the procedure:

GRANT EXECUTE ON dbo.uxp_cmdshell TO [your_user_or_role!];

Now when your_user logs in, and tries to execute willy-nilly calls to xp_cmdshell:

EXEC master.dbo.xp_cmdshell 'dir c:\';

They will get:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

However if they pass the same command to your new procedure:

EXEC dbo.uxp_cmdshell 'dir c:\';

It will work just fine (assuming your proxy account is set up correctly and/or the SQL Server service account has adequate permissions).

This can take some work, but effectively this allows you to control exactly what your users can do with xp_cmdshell.

StackExchange Database Administrators Q#34633, answer score: 9