Enable the xp cmdshell in Sql Server

XP_CMDSHELL is the extended stored procedure which is used to execute the Operating System command from the SQL Server TSQL language. It is stored in Master database.

Example:

exec master.dbo.xp_cmdshell 'dir D:\backup\*.bak'

exec master.dbo.xp_cmdshell 'mkdir "D:\backup\29dec2013"'

 

Steps for Enable the XP_CMDSHELL 

1. Go to the SQL Server Management Studio

2. Connect with instance with super user (sa user)

3. Use the master database

4. Execute the following command for enable xp_cmdshell

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

 

Steps for disable the XP_CMDSHELL

1. Go to the SQL Server Management Studio

2. Connect with instance with super user (sa user)

3. Use the master database

4. Execute the following command for enable xp_cmdshell

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

 

Following error occured on execution of xp_cmdshell if it 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.