Friday, December 20, 2013

SQL Server Error: 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.)

I was trying to enable xp_cmdshell on one  of our SQL Server in order to calculate folder size and got below error, just thought to share on blog to help users if somebody also getting similar errors.


I ran below command and got following error.

Command:

USE master
GO
EXEC xp_cmdshell N'dir C:\Ashish_Files'

Error:

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.


Resolution:
Obviously you need to enable xp_cmdshell using the server configuration option, please use below command to enable that.

USE master
GO

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE

You'll get following message.

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.




3 comments:

  1. Thanks for posting a detailed issue description + resolution. That helped a lot.

    ReplyDelete
  2. Your info might solely be used or disclosed for the aim for which it was collected except you've have} in any other case consented, or when it's required or permitted by 솔카지노 law. Your info is retained only for the time frame required to fulfill the aim for which it was collected. By creating a piece surroundings where staff can constantly learn, develop and thrive, we benefit as a enterprise and society benefits from their rising capabilities and contributions. We will do our greatest reply to|to answer|to reply to} every request in a well timed and respectful trend. Our companies replicate the input and expertise of those organizations.

    ReplyDelete