SQL SERVER – Enable xp_cmdshell using sp_configure « Journey to SQL Authority with Pinal Dave

SQL SERVER – Enable xp_cmdshell using sp_configure

April 26, 2007 by pinaldave

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.
---- To allow advanced options to be changed.

EXEC sp_configure ’show advanced options’, 1

—- To update the currently configured value for advanced options.

RECONFIGURE

—- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

—- To update the currently configured value for this feature.

RECONFIGURE

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, T SQL, Technology | Tagged SQL Advanced Option | 10 Comments

10 Responses

on June 27, 2007 at 7:00 am | Reply SQL SERVER - Running Batch File Using T-SQL - xp_cmdshell bat file Journey to SQL Authority with Pinal Dave

[...] 27th, 2007 by pinaldave In last month I received few emails emails regarding SQL SERVER – Enable xp_cmdshell using sp_configure. The questions are i) What is the usage of xp_cmdshell and 2) How to execute BAT file using [...]
on September 11, 2007 at 1:33 pm | Reply Daljeet

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
on September 12, 2007 at 4:14 am | Reply Vaibhav

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
on March 14, 2008 at 12:06 pm | Reply Arkadyuti Bhattacharya

Thanks Sir,

It helped me a lot.
on August 4, 2008 at 7:20 pm | Reply dimitris

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!
on September 22, 2008 at 2:37 pm | Reply Vivek Rathore

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.
on October 17, 2008 at 7:30 pm | Reply praveen

it is very helpfull to delete files from server through sql server.

thanks guys!.

Thanks and Regards
praveen
on December 9, 2008 at 4:51 am | Reply Jimmy

Thanks!
on January 13, 2009 at 10:35 pm | Reply Marcia

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.
on August 20, 2009 at 10:50 pm | Reply Krishan

Hi, We need to disbale other extended sp. like
xp_regread, xp_regwrite, xp_regaddmultistring, xp_regdeletekey

Please help me how will do