How to Enable & Disable XP_CMDSHELL using SP_CONFIGURE


July 02, 2013

Introduction

In this tip we will take a look at the step to follow to enable or disable XP_CMDSHELL using SP_CONFIGURE system stored procedure. In order to use XP_CMDSHELL you need to be a system administrator. Read the following article which explains how to allow non-administrators to use XP_CMDSHELL.

Error Message Received when XP_CMDSHELL is disabled

You would end up getting the below mentioned error message when XP_CMDSHELL is disabled.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 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.

How to Enable XP_CMDSHELL using SP_CONFIGURE

A database administrator can enable XP_CMDSHELL using SP_CONFIGURE system stored procedure. T o enable XP_CMDSHELL execute the below mentioned script.

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

Query Output

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Newsletter Signup

Recommendation: - MyTechMantra.com recommends the use of performance monitoring tools to be run on the SQL Server to identify potential bottlenecks. Hence we recommend you to take a look at "Database Performance Analyzer" one such Free Tool to avoid issues proactively.

How to Disable XP_CMDSHELL using SP_CONFIGURE

It is always considered as a best practice to disable XP_CMDSHELL when not in use. A database administrator can disable XP_CMDSHELL using SP_CONFIGURE system stored procedure. To disable XP_CMDSHELL execute the below mentioned script.

Use Master
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Query Output

Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

Share this Article

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."