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.