I am not getting into the discussion of pros and cons of xp_cmdshell. This command helps you to run broad level features inside your SQL server. You can run OS level commands, SSIS packages etc.
Here is how you can enable, disable and use.
To check, if xp_cmdshell is enabled and available to use;
SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM sys.configurations
WHERE name = 'xp_cmdshell';
Turn on xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE
Turn off xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE
Here is a simple use case;
EXEC xp_cmdshell 'dir c:\'
Here is how you can enable it, use it and disable;
DECLARE @originalSetting INTEGER = 0;
SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'xp_cmdshell' ;
IF @originalSetting = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
END
EXEC xp_cmdshell 'dir c:\';
IF @originalSetting = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
END
You can create a stored procedure and use;
CREATE PROCEDURE sp_SuperCmdShell
(
@commandToRun VARCHAR(1000)
)
AS
BEGIN
-- check to see if xp_cmdshell is enabled, if its not
-- then enable it and run the command then set it back
-- to not enabled.
DECLARE @originalSetting INTEGER = 0;
SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'xp_cmdshell' ;
IF @originalSetting = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
END
EXEC xp_cmdshell @commandToRun;
IF @originalSetting = 0
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
END
END
GO
exec #sp_SuperCmdShell 'dir c:\';
This is not a new feature. It came out with early release of SQL server. If you see the usage command, they are OS level command. With the super privileged access any body can run a “format c:\” OS command and leave your database server in unrecoverable state. My suggestion would be to use it with least privileged proxy account.
