The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.
Here is the code which is displayed in the code above. You can run it in your SQL Server Management Studio (SSMS).
---- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO ---- To update the currently configured value for advanced options. RECONFIGURE GO ---- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO ---- To update the currently configured value for this feature. RECONFIGURE GO
Honestly, now a days there is not much need of this particular command. I have seen lots of people using powershell to do many different tasks. However, there was a time when Powershell did not exist and we had to do lots of tasks with the help of the command shell.
Though, I have never learned Powershell or command prompt language personally, I have some knowledge of this subject from my database administration experience. I have learned as much as powershell as much as I need in my daily use.
Have you ever enabled this command to accomplish your tasks? If yes, would you please leave a comment here with your experience.
Reference: Pinal Dave (https://blog.sqlauthority.com)
52 Comments. Leave new
Hi,
Well i tried the EXEC sp_configure ‘show advanced options’, 1 command and it took a lot of time went over 15 mins so i am stopped it, am not sure that is it normal for it to take that long or i did something wrong, PLz let me know an average time taken by EXEC sp_configure command to execute on a 2005 SP1 test server which has almost no load at all.
thanks
Hello pinal
i m working on sql server 2005 …….can u tell me how to recover data when database is been crashed ………….tell me the steps to follow ….i m not able to restore a last transaction backup because databse is in emergency state…………………………..should i need to change anything in master……….please do let me know
thanx in adv…….
vaibhav Mathur
D.B.A
Thanks Sir,
It helped me a lot.
Hi i want to enable the xp_cmdshell on sql server 2000.
Please give me information if you can.
thanks a lot just for reading this!
Hi,
I am trying to run a batch file and using the following syntax:
EXECUTE master..xp_cmdshell ‘C:\AAT_PDF_Thumbnail_Generator\test.bat’
It does not show any error message and the query goes running. Please help me to figure out that where i am wrong.
And will u please tell me the steps regarding the Access rights to execute it with the user of the database. Although i have given the execute rights to the user and the same process is running fine in SQL 2000.
Thanks n Reagrds,
Vivek Rathore.
it is very helpfull to delete files from server through sql server.
thanks guys!.
Thanks and Regards
praveen
Thanks!
For security reasons, we do not want to enable xp_cmdshell. However, for other practical reasons, we want to use it for some jobs. My thought was to enable xp_cmdshell at the start of the job, and then disable it when the job completes. How much overhead does the reconfigure take? Also, do you know what would happen if one job does the disable while a second job is in the middle of using the xp_cmdshell? Would the second job err out or would the disabling/reconfiguring err out? I tried to test it, but I can’t seem to come up with a long-running job to test this out.
Hi, We need to disbale other extended sp. like
xp_regread, xp_regwrite, xp_regaddmultistring, xp_regdeletekey
Please help me how will do
Dear Pinal and other users of SQL Server,
I have a question about xp_cmdshell.
I read the article about “SQL SERVER – Enable xp_cmdshell using sp_configure” but what I need not enable or disable this feature. I need to know if it is enabled or not. This is possible using sql statement?
I look back and I thank your attention.
Hi Adalton,
If you need to disable this feature, you just need to configure it as:
EXEC sp_configure ‘xp_cmdshell’, 0
GO
RECONFIGURE
GO
To Enable it, as Pinal said:
EXEC sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE
GO
Thanks,
Tejas
SQLYoga.com
Adalton you can try this:
SELECT Value FROM SYS.CONFIGURATIONS WHERE Name = ‘show advanced options’
SELECT Value FROM SYS.CONFIGURATIONS WHERE Name = ‘xp_cmdshell’
Where do I execute this from?
Hello Denise
sys. schema is accessible from all databases. So these queries to check configuration can be executed in any database.
Regards,
Pinal Dave
Hi Adalton Jr.
If you want to view all the configurations and if they are enabled or not, you can just run the sp_configure without parameters.
For example:
EXEC dbo.sp_configure
Hi,
I am getting an error
“The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.” while running
exec sp_configure ‘xp_cmdshell’,1
please reply me if you have any idea.
hey vikas this side.
you just do one thing just enable ur advane option in sql. type
exec sp_configure ‘show advance option’ , 1
reconfigure with override
go
this will enable your advance option after that you can run ‘exec sp_configure ‘xp_cmdshell’,1’
hope this will help you
Hi,
I could figure out to get to SQLPlus of SQL 2005 to execute the above posted command.
Could anyone of you give me instruction to execute the SQL command.
Thanks
hello sir,
i am working on sq 2005 standard addition.AWE is enable on my server and i want to set a schedule for automatic full database backup on daily bases but when i reconfigure agent_xp server throw an error message
“Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.”
even i have checked lock page in memory it is enabled. Please sir suggest me what i can do for this
when i enter this in SQL Server 2000 and OS: windows XP Professional
EXEC xp_cmdshell ‘notepad.exe’
it shows ‘executing query’ and so on…………………..
on ending of this statement
plz help
how can i run exernal files/exes from query
thanks
krishan
Hi Pinal,
while running batch file through sqlserver ,I am getting the following error ..
EXEC master.dbo.xp_cmdshell ‘C:RunMasterPackage.bat’
output
‘C:\RunMasterPackage.bat’ is not recognized as an internal or external command,
operable program or batch file.
NULL