How to Enable XP_CMDSHELL & Disable XP_CMDSHELL using SP_CONFIGURE in SQL Server

How to Enable xp_cmdshell | How to Disable xp_cmdshell

In this tip we will take a look at the step to follow to Enable XP_CMDSHELL and how to 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?

Steps to enable XP_CMDSHELL in SQL Server are:-

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.

How to Disable XP_CMDSHELL using SP_CONFIGURE?

Steps to disable XP_CMDSHELL in SQL Server are:-

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.

Conclusion

As a best practice one should always disable XP_CMDSHELL access and it should be enable or disable using SP_CONFIGURE system stored procedure.

Related Articles and Tips

• Different ways to retrieve System and SQL Server Information
• How to configure SQL Server Agent to Restart SQL Server and SQL Server Agent Services Automatically
• How to connect to a named instance of SQL Server?
• Fix Index cannot be reorganized because page level locking is disabled error in SQL Server
• Grant Access to DMVs in SQL Server for Non Admin
• How to Improve SQL Server Replication Performance
• Unable to create or open SSIS projects or maintenance plans in SQL Server 2012
• Performance Dashboard Reports in SQL Server 2012
• How to Attach Database Without a Transaction Log File in SQL Server
• New Features in SQL Server 2012 Reporting Services for Developers
• New Features in SQL Server 2012 for Database Administrators
• New Features in SQL Server 2012 for Database Developers