Enable 'xp_cmdshell' SQL Server

I want to execute EXEC master..xp_cmdshell @bcpquery

But I am getting the following error:

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.

Is there any way to activate this, or execute something before enabling the feature?

How to solve it?

Solution 1:

You need to enable it. Check out the Permission section of the xp_cmdshell MSDN docs:

http://msdn.microsoft.com/en-us/library/ms190693.aspx:

-- 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

Solution 2:

You can also hide again advanced option after reconfigure:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Solution 3:

Right click server -->Facets-->Surface Area Configuration -->XPCmshellEnbled -->true

Solution 4:

As listed in other answers, the trick (in SQL 2005 or later) is to change the global configuration settings for show advanced options and xp_cmdshell to 1, in that order.

Adding to this, if you want to preserve the previous values, you can read them from sys.configurations first, then apply them in reverse order at the end. We can also avoid unnecessary reconfigure calls:

declare @prevAdvancedOptions int
declare @prevXpCmdshell int

select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 1
    reconfigure
end

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
end

/* do work */

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 0
    reconfigure
end

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 0
    reconfigure
end

Note that this relies on SQL Server version 2005 or later (original question was for 2008).