Enable and Disable xp_cmdshell

xp_cmdshell is one of those somewhat hidden gems/power-tools in SQL Server that can both make life easy and very insecure all at the same time.

You see, this function allows any SQL Server administrator to run any command/program/shell-script/batch-file that the SQL Server account has access to. It seems counter-intuitive at first but I’ve seen this most often becoming a problem in development environments where users might have sysadmin rights on the SQL Server but not necessarily to the server that’s running SQL Server. Given that SQL Server is often given a fair amount of rights on the server (far greater than a standard windows user).

By default (once enabled) xp_cmdshell requires CONTROL SERVER to execute, but permission can be given out simply by granting execute permission.

GRANT exec ON xp_cmdshell TO N'<some_user>';

While permission can be doled out in this way, non-sysadmins will not execute as SQL Server but rather as account name ##xp_cmdshell_proxy_account##. If the proxy account has not been set, then executions by non-sysadmins will fails.

Setting the proxy account and password is as simple as:

EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\user','SomePaSSword!';

To enable xp_cmdshell:

--We first must show SQL Servers advanced options:
EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
GO
--Then we can enable xp_cmdshell:
EXEC sp_configure 'xp_cmdshell', '1' 
RECONFIGURE
GO

To disable xp_cmdshell:

--We first must show SQL Servers advanced options:
EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
GO
--Then we can disable xp_cmdshell:
EXEC sp_configure 'xp_cmdshell', '0' 
RECONFIGURE
GO

Happy executing! (ง ‘̀͜ ‘́ )ง