How to Enable xp_cmdshell

There's a great little feature of SQL Server that allows you to execute command-line statements, allowing you to run batch files, get directory listings or call executables.  The feature is accessed via the xp_cmdshell Extended Stored Procedure, the syntax is pretty simple and looks like this…

EXEC
xp_cmdshell
'dir *.exe';

In the wrong hands xp_cmdshell could cause havoc, for that reason it is disabled by default and in order to enable it you will need to run the following…

– To allow advanced options to be changed.
EXEC
sp_configure
'show advanced options',
1
GO
– To update the currently configured value for advanced options.
RECONFIGURE
GO
– To enable the feature.
EXEC
sp_configure
'xp_cmdshell',
1
GO
– To update the currently configured value for this feature.
RECONFIGURE
GO