none
how to check xp_cmdshell is turn on or off through TSQL (SQL server 2008)

    Question

  • how to check xp_cmdshell is turn on or off through TSQL
    Mark as Answer If appropriate - SSS.Ezhil
    Monday, July 19, 2010 11:57 AM

Answers

  • EXEC sp_configure ‘show advanced options’, 1

    Monday, July 19, 2010 12:03 PM
  • USE master; 
    GO 
    EXEC sp_configure 'show advanced option', '1'; 
    EXEC sp_configure; 
    Column name Data type Description

    name

    nvarchar(35)

    Name of the configuration option.

    minimum

    int

    Minimum value of the configuration option.

    maximum

    int

    Maximum value of the configuration option.

    config_value

    int

    Value to which the configuration option was set using sp_configure (value in sys.configurations.value). For more information about these options, see Setting Server Configuration Options and sys.configurations (Transact-SQL).

    run_value

    int

    Currently running value of the configuration option (value in sys.configurations.value_in_use).

    For more information, see sys.configurations (Transact-SQL).

    Monday, July 19, 2010 12:04 PM
  • To add on to Manikandan's response:

    EXEC sp_configure 'show advanced',1 --to see xp_cmdshell configuration option
    RECONFIGURE
    EXEC sp_configure 'xp_cmdshell',1 --turn on
    RECONFIGURE
    EXEC sp_configure 'xp_cmdshell',0 --turn off
    RECONFIGURE
    
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, July 19, 2010 12:06 PM

All replies

  • EXEC sp_configure ‘show advanced options’, 1

    Monday, July 19, 2010 12:03 PM
  • USE master; 
    GO 
    EXEC sp_configure 'show advanced option', '1'; 
    EXEC sp_configure; 
    Column name Data type Description

    name

    nvarchar(35)

    Name of the configuration option.

    minimum

    int

    Minimum value of the configuration option.

    maximum

    int

    Maximum value of the configuration option.

    config_value

    int

    Value to which the configuration option was set using sp_configure (value in sys.configurations.value). For more information about these options, see Setting Server Configuration Options and sys.configurations (Transact-SQL).

    run_value

    int

    Currently running value of the configuration option (value in sys.configurations.value_in_use).

    For more information, see sys.configurations (Transact-SQL).

    Monday, July 19, 2010 12:04 PM
  • To add on to Manikandan's response:

    EXEC sp_configure 'show advanced',1 --to see xp_cmdshell configuration option
    RECONFIGURE
    EXEC sp_configure 'xp_cmdshell',1 --turn on
    RECONFIGURE
    EXEC sp_configure 'xp_cmdshell',0 --turn off
    RECONFIGURE
    
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, July 19, 2010 12:06 PM
  • Perhaps something like this:

    SELECT RTRIM(name) +'  :  '+ CASE value WHEN 0 THEN 'OFF' WHEN 1 THEN 'ON' END
    FROM master.sys.configurations
    WHERE name = 'xp_cmdshell'

    Wednesday, August 21, 2013 3:10 AM