In this article, I will explain how I encountered the error “SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’.” Also, I will explain what I did to resolve this error.
Recently, when I was working with SQL Server stored procedures, I wanted to create a stored procedure that creates a directory in the local file system. I searched on the web, how to create a folder in the Windows local file system.
I found that there is a system-defined stored procedure in SQL server named xp_cmdshell that we can use to execute the commands on the Windows command line. Therefore, I tried to pass the Windows command to create a folder to the stored procedure xp_cmdshell. But when I tried to create the folder, I got the following error message:
I was trying to execute the command as:
USE master GO EXEC xp_cmdshell 'MD "C:/NewFolder"'
Solution: SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’
After some research on the internet, I found that before using the xp_cmdshell stored procedure, I need to enable it. Also, it is an advanced option. You cannot enable it directly. First, you have to enable the Show Advanced Options. Only then, you can enable the xp_cmdshell.
I used the following commands to enable these options:
EXEC sp_configure 'Show Advanced Options', '1' RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', '1' RECONFIGURE GO
After executing the above commands, I was able to resolve the error and I was able to use the xp_cmdshell and created a folder in the local file system.
You may like the following SQL server tutorials:
- SQL Server stored procedure if else
- How to get list of users in SQL Server
- How to view stored procedure in SQL Server
- SQL Server select from stored procedure
- SQL Server stored procedure output parameter
- How to check if SQL Server is running
- SQL Server check user permissions on table
This is how to fix the error, SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’.
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.