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.
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
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.
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.
Add comment