How to enable xp_cmdshell on SQL Server 2005/2008?
If xp_cmdshell is disabled on SQL Server you get the following error:
Query:
[sql]
EXEC xp_cmdshell ‘dir’
[/sql]
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.
In order to enable this SQL Server has to be configured to allow access to procedure xp_cmdshell.
Following are the steps for that:
[sql]
EXEC sp_configure ‘show advanced options’, 1
reconfigure
GO
[/sql]
This enables to show advanced options by sp_configure.
Now if you execute sp_configure
[sql]
EXEC sp_configure
[/sql]
The following will be the output in the results window:

Run the following query to enable this:
[sql]
EXEC sp_configure ‘xp_cmdshell’,1
reconfigure
GO
[/sql]
Now you will be able to execute xp_cmdshell.
More about using sp_configure can be found here
great tip, thanks for sharing
one question; if i want to activate xp_cmdshell on sql2008 r2, how can i do that? because this procedure failed.
Thanks