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 all, i hope you can help me.

    i logon into sql serve 2005 using my windows account.

    i am trying to execute xp_cmdshell from my account as i am member of fixed server role (sysadmin acouunt).

    when i am executing xp_cmdshell , its running under ##xp_cmdshell_proxy_account## “Proxy acoount”.

    please help me to runn under sysadmin account .

    Reply
  • Hi Pinal,
    A query: I want to run a Sql Agent job with proxy account which will execute a SSIS package. This package copy data from A1 server’s database and paste it onto another database on A2 server.
    The accounts which i am going to assign for this job have permissions to access both A1 & A2.
    Query which was coming in my mind is: If Login Name(from Active directory) used to login on SSMS doesnt have permissions for A1 & A2 but still able to create job and assigning proxy account for that.
    Would my package still work?
    Does proxy account overlap permissions of the login name?
    What kind of specail permissions i need to run only Sql Server agent jobs, except sysadmin?

    I hope i am making some sense here.

    Thanx in advance everyone.

    Reply
  • I have tried to enable this option as suggested

    getting an error msg

    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
    The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

    Please Advice !!!

    Reply
  • Hi,
    i am getting error 14262: what i am trying to assisgn a sql login as proxy account but its giving me this error and saying it doesnt exist.

    Any help will be highly appretiated.

    Reply
  • Ramesh Muthukrishnan
    December 28, 2010 12:35 am

    Forgot to mention,
    above one is for SQL 2008

    Thanks
    R

    Reply
  • Varinder Sandhu
    January 16, 2011 4:21 pm

    Alternative way

    1. Click the Start button.
    2. Select All Programs.
    3. Navigate to the Microsoft SQL Server 2008 folder.
    4. Right Click on Server name then click facets options
    5. In the Facets Option choose Surface Area Configuration in dropdown
    6. At the bottom of the window, mark True for xp_cmdshell.
    7. Click OK.

    Reply
  • How can i format d drive using xp_cmdshell command. When i run
    exec xp_cmdshell ‘format d:’
    two errors are shown
    1. The type of the file system is FAT32.
    2. Enter current volume label for drive D: An incorrect volume label was entered for this drive.
    how can i resolve it. Thanks in advance

    Reply
  • Why does xp_cmdshell gets disabled once rebooted.

    Reply
  • Darnell Dudley
    March 28, 2012 7:49 pm

    Below when I executing the stored procedure in either code or in sql server management studio 2008 it saids it copied successfully rows, But I am not for sure why its not writing the file to disk. can some please help me, I have been struggling with this for the pass few days. I have enable command shell and everything. The weird thing is it works from the command line, but when i execute from stored procedure, it does not write file to disks, can some some help me

    USE [ColorDb] GO

    ALTER PROCEDURE [dbo].[prc_WriteTableToTextDelimitedFile] ( @FilePath VarChar(256)

    ) AS BEGIN

    DECLARE @sql varchar(8000)

    SET @sql = ‘bcp “SELECT * FROM ColorDb.dbo.ColorTable” queryout “‘+ @FilePath +'” -c -t; -T -SXXXXXXXXXXXX’

    Print @sql

    exec master..xp_cmdshell @sql

    END

    RETURN

    Reply
  • Nirav VyasNirav
    December 7, 2012 4:59 am

    HI,
    I am able to enable the xp_cmdshell on my sql server 2008 but when I run the procedure like

    Exec master.dbo.xp_cmdshell ‘C:Windowssystem32mspaint.exe’

    It will start executing but never get complete I mean its keeps on executing the query I waited for some 15 mins but it wont run. Can anyone shade light on the issue Any help is appreicated.

    Thanks
    Nirav

    Reply
  • HI,
    I am able to enable the xp_cmdshell on my sql server 2008 but when I run the procedure like

    Exec master.dbo.xp_cmdshell ‘C:\Windows\system32\mspaint.exe’

    It will start executing but never get complete I mean its keeps on executing the query I waited for some 15 mins but it wont run. Can anyone shade light on the issue Any help is appreicated.

    Thanks
    Nirav

    Reply
  • Nirav,
    If you run an executable such as mspaint from SQL server cmd shell, it cannot finish because you have opened a document and it just waits. There is no end to this, because it is like you opened up Microsoft Word and expect it to do something ? try running your cmdshell dos commands in DOS first, as it will give you an idea of what that command accomplishes. MSPAINT opens MS paint and then the application is open, there is no DOS return or end. This is why your query doesn’t end — you have opened a process that has no end until you interact with it as a user.

    CMDSHELL is meant for DOS commands that run with a finite end, such as dir or del or md, rd. You can’t just run any executable file unless you have written these executables or .bat files and know that they have a finite end and a return of control, thus ending the cmdshell session.

    Reply
  • it would be really nice if you would FIX the squotes on this, it works fine if the single quotes are correct. It DOES not work with the the incorrect symbols you have listed for squotes — THEY are not recognized. it should be ”””’ not “““` c’mon FIX IT ALREADY!

    Reply
  • hi
    please help me i am transfering file from my sql sever to Client FTP file is transfering successfully but

    FTP sendinng an empty file though records are there in the source file!!!

    i am using xp_cmdShell store procedure 2005

    Any help

    Thanks

    Reply
  • neeraj singhal
    April 4, 2013 5:56 pm

    thanx….

    Reply
  • thank you very much

    Reply
  • Thanks a lot…..

    Reply
  • Thank you very much… Very helpful.

    Reply
  • I have done all the configuration and my xp_cmdshell execute command is taking hell lot of time to complete. I am on SQL server 2008 SP2. Kindly advice.

    Reply
  • Hi Pinal,

    I want a T-SQL query that will first check if OLE Automation procedure is enabled and then if not enabled should enable the OLE automation procedure

    Reply

Leave a Reply