SQLSERVERLEARNER

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