Enabling xp_cmdshell in SQL 2008

On SQL 2008, the only option to enable the xp_cmdshell feature in SQL 2008 is to use sp_configure:

use master
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE

Use the following snippet to enable the xp_cmdshell within a SQL script. (Note that GO is a batch separator in the query window, not a T-SQL command, and should be removed before using this code snippet in your SQL script.)

IF (SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  master.sys.configurations
WHERE  name = 'xp_cmdshell') = 0
BEGIN
	EXEC master.dbo.sp_configure 'show advanced options',1
	RECONFIGURE
	-- To enable the feature.
	EXEC master.dbo.sp_configure 'xp_cmdshell', 1
	RECONFIGURE
END

And, if you need to enable the xp_cmdshell on a remote SQL server instance, try the following. Note that you’ll need to remotely invoke the RECONFIGURE command using the sp_configure stored procure. The sp_configure procedure expects the parameter @statement of type ntext, nchar, or nvarchar. In this case, I skipped the need to declare an @statement variable and just used the N prefix.

IF (SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  [SQLNVT08].master.sys.configurations
WHERE  name = 'xp_cmdshell') = 0
BEGIN
	-- To allow advanced options to be changed.
	EXEC [SQLNVT08].master.dbo.sp_configure 'show advanced options',1
	EXEC [SQLNVT08].master.dbo.sp_executesql N'RECONFIGURE'
	-- To enable the feature.
	EXEC [SQLNVT08].master.dbo.sp_configure 'xp_cmdshell', 1
	EXEC [SQLNVT08].master.dbo.sp_executesql N'RECONFIGURE'
END

See the following article for a discussion on the use the ‘N‘ prefix with the sp_executesql procedure.

Why do some SQL strings have an ‘N’ prefix?

Leave a Reply

Your email address will not be published. Required fields are marked *