SQL SERVER – Enable xp_cmdshell using sp_configure

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.

SQL SERVER - Enable xp_cmdshell using sp_configure xp_cmdshell

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)

, , , , , ,
Previous Post
SQL SERVER – 2005 – DBCC ROWLOCK – Deprecated
Next Post
SQL SERVER – Difference Between Unique Index vs Unique Constraint

Related Posts

52 Comments. Leave new

  • Hi

    Could any one reply for Marcia’s question
    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.

    I am having the same issue Please give some solution

    Reply
  • Shyamaprasad Sarkar
    August 12, 2015 1:49 pm

    Hi Pinal,

    Is xp_cmdShell available with SQL Server 20088 Web Edition?

    If not, how can we use it?

    Thanks,
    Shyamaprasad

    Reply
  • excellent .it’s working fine

    Reply
  • We did have it turned on everywhere, but we are turming it off now and replacing it with a PowerShell script.

    Reply
  • Hi Dave, I started to use it :) and your blog help me to configure my db.

    Reply
  • Hi,

    How is it different between ‘RECONFIGURE WITH OVERRIDE’ and ‘RECONFIGURE’?

    Thanks

    Reply
    • WITH OVERRIDE: Disables the configuration value checking (for values that are not valid or for nonrecommended values) for the recovery interval advanced configuration option.

      Reply
  • Hi. We are going to try it out in test for executing the dtexec utility synchronized thru xp_cmdshell. We’ll turn it off if it doesn’t work out. Powershell and the SSIS Catalog have a complicated relationship. If you need to execute a package from a remote server synchronized, the number of options dwindles. It appears you can directly or indirectly execute some provided ssisdb procs (they build pkg execute commands and execute those commands) by providing a “synchronized” flag. Or you can do essentially the same thing by issuing a dtexec call with a synchronized flag being passed. Interestingly, the latter looks like the same command that sql agent issues when you choose ssis package from the catalog on an agent step.

    Reply

Leave a Reply

Menu