Can we minimise the risk of using xp_cmdshell?
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
.
You can also setup a proxy account using sp_xp_cmdshell_proxy_account to allow non-administrators to use xp_cmdshell. This will allow you to setup a less privileged Windows account rather than xp_cmdshell always using the SQL Server service account. This is similar to setting up proxy accounts for SQL Agent jobs.