locked
ENABLE xp_cmdshell no SQL 2005 RRS feed

  • Pergunta

  • Como faço pra habilitar a opção ENABLE xp_cmdshell no SQL SERVER 2005 com comandos T-SQL? sem usar a interface do Sql Server Suface Area Configuration
    • Movido Gustavo Maia Aguiar sexta-feira, 1 de outubro de 2010 16:57 (De:SQL Server - Desenvolvimento Geral)
    segunda-feira, 25 de junho de 2007 12:57

Respostas

  • segue ...

     

    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

    Abs/

    segunda-feira, 25 de junho de 2007 13:03
  • Paulo,

     

    Por se trata de uma configuração que deve ser realizado no servidor SQL, utilize a stored procedure sp_configure.

     

    Veja o exemplo extraído do Books On-Line do SQL Server 2005.

     

    -- 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

    Quando você utiliza a opção Reconfigure, o SQL Server entende que esta configuração deve ser aplicada em ter a necessidade de parar o serviço e reinicialo novamente, ou seja, o alteração é realizada imediatamente. Mas isso vai depender do tipo de opção que você esta alterando.

     

    Abaixo, você encontrará uma tabele contendo todas as opções que podem ser configurados a nível de servidor, além disso, determina também se esta opção é necessário reinicializar ou não o servidor.

     

    Configuration Options Table

    The following table lists all available configuration options, the range of possible settings, and default values. Configuration options are marked with letter codes as follows:

    • A= Advanced options, which should be changed only by an experienced database administrator or a certified SQL Server technician, and which require setting show advanced options to 1.

    • RR = Options requiring a restart of the Database Engine.

    • SC = Self-configuring options.

      Configuration option Minimum value Maximum value Default

      Ad Hoc Distributed Queries (A)

      0

      1

      0

      affinity I/O mask (A, RR)

      -2147483648

      2147483647

      0

      affinity64 I/O mask (A, only available on 64-bit version of SQL Server)

      -2147483648

      2147483647

      0

      affinity mask (A)

      -2147483648

      2147483647

      0

      affinity64 mask (A, only available on 64-bit version of SQL Server)

      -2147483648

      2147483647

      0

      Agent XPs (A)

      0

      1

      0

      (Changes to 1 when SQL Server Agent is started. Will be 1 if SQL Server Agent is set to automatic start during setup.)

      allow updates (Obsolete. Do not use. Will cause an error during reconfigure.)

      0

      1

      0

      awe enabled (A, RR)

      0

      1

      0

      blocked process threshold (A)

      0

      86400

      0

      c2 audit mode (A, RR)

      0

      1

      0

      clr enabled

      0

      1

      0

      common criteria compliance enabled (A, RR)

      0

      1

      0

      cost threshold for parallelism (A)

      0

      32767

      5

      cross db ownership chaining

      0

      1

      0

      cursor threshold (A)

      -1

      2147483647

      -1

      Database Mail XPs (A)

      0

      1

      0

      default full-text language (A)

      0

      2147483647

      1033

      default language

      0

      9999

      0

      default trace enabled (A)

      0

      1

      1

      disallow results from triggers (A)

      0

      1

      0

      fill factor (A, RR)

      0

      100

      0

      ft crawl bandwidth (max), see ft crawl bandwidth(A)

      0

      32767

      100

      ft crawl bandwidth (min), see ft crawl bandwidth(A)

      0

      32767

      0

      ft notify bandwidth (max), see ft notify bandwidth(A)

      0

      32767

      100

      ft notify bandwidth (min), see ft notify bandwidth(A)

      0

      32767

      0

      index create memory (A, SC)

      704

      2147483647

      0

      in-doubt xact resolution (A)

      0

      2

      0

      lightweight pooling (A, RR)

      0

      1

      0

      locks (A, RR, SC)

      5000

      2147483647

      0

      max degree of parallelism (A)

      0

      64

      0

      max full-text crawl range (A)

      0

      256

      4

      max server memory (A, SC)

      16

      2147483647

      2147483647

      max text repl size

      0

      2147483647

      65536

      max worker threads (A, RR)

      128

      32767

      (1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.)

      0

      Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256+(<processors> -4) * 8) for 32-bit SQL Server and twice that for 64-bit SQL Server.

      media retention (A, RR)

      0

      365

      0

      min memory per query (A)

      512

      2147483647

      1024

      min server memory (A, SC)

      0

      2147483647

      8

      nested triggers

      0

      1

      1

      network packet size (A)

      512

      32767

      4096

      Ole Automation Procedures (A)

      0

      1

      0

      open objects (A, RR, obsolete)

      0

      2147483647

      0

      PH_timeout (A)

      1

      3600

      60

      precompute rank (A)

      0

      1

      0

      priority boost (A, RR)

      0

      1

      0

      query governor cost limit (A)

      0

      2147483647

      0

      query wait (A)

      -1

      2147483647

      -1

      recovery interval (A, SC)

      0

      32767

      0

      remote access (RR)

      0

      1

      1

      remote admin connections

      0

      1

      0

      remote login timeout

      0

      2147483647

      20

      remote proc trans

      0

      1

      0

      remote query timeout

      0

      2147483647

      600

      Replication XPs Option (A)

      0

      1

      0

      scan for startup procs (A, RR)

      0

      1

      0

      server trigger recursion

      0

      1

      1

      set working set size (A, RR, obsolete)

      0

      1

      0

      show advanced options

      0

      1

      0

      SMO and DMO XPs (A)

      0

      1

      1

      SQL Mail XPs (A)

      0

      1

      0

      transform noise words (A)

      0

      1

      0

      two digit year cutoff (A)

      1753

      9999

      2049

      user connections (A, RR, SC)

      0

      32767

      0

      User Instance Timeout (A, only appears in SQL Server 2005 Express Edition)

      5

      65535

      60

      user instances enabled (A, only appears in SQL Server 2005 Express Edition)

      0

      1

      0

      user options

      0

      32767

      0

      Web Assistant Procedures (A)

      0

      1

      0

      xp_cmdshell (A)

    segunda-feira, 25 de junho de 2007 13:26
    Moderador

Todas as Respostas

  • segue ...

     

    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

    Abs/

    segunda-feira, 25 de junho de 2007 13:03
  • Paulo,

     

    Por se trata de uma configuração que deve ser realizado no servidor SQL, utilize a stored procedure sp_configure.

     

    Veja o exemplo extraído do Books On-Line do SQL Server 2005.

     

    -- 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

    Quando você utiliza a opção Reconfigure, o SQL Server entende que esta configuração deve ser aplicada em ter a necessidade de parar o serviço e reinicialo novamente, ou seja, o alteração é realizada imediatamente. Mas isso vai depender do tipo de opção que você esta alterando.

     

    Abaixo, você encontrará uma tabele contendo todas as opções que podem ser configurados a nível de servidor, além disso, determina também se esta opção é necessário reinicializar ou não o servidor.

     

    Configuration Options Table

    The following table lists all available configuration options, the range of possible settings, and default values. Configuration options are marked with letter codes as follows:

    • A= Advanced options, which should be changed only by an experienced database administrator or a certified SQL Server technician, and which require setting show advanced options to 1.

    • RR = Options requiring a restart of the Database Engine.

    • SC = Self-configuring options.

      Configuration option Minimum value Maximum value Default

      Ad Hoc Distributed Queries (A)

      0

      1

      0

      affinity I/O mask (A, RR)

      -2147483648

      2147483647

      0

      affinity64 I/O mask (A, only available on 64-bit version of SQL Server)

      -2147483648

      2147483647

      0

      affinity mask (A)

      -2147483648

      2147483647

      0

      affinity64 mask (A, only available on 64-bit version of SQL Server)

      -2147483648

      2147483647

      0

      Agent XPs (A)

      0

      1

      0

      (Changes to 1 when SQL Server Agent is started. Will be 1 if SQL Server Agent is set to automatic start during setup.)

      allow updates (Obsolete. Do not use. Will cause an error during reconfigure.)

      0

      1

      0

      awe enabled (A, RR)

      0

      1

      0

      blocked process threshold (A)

      0

      86400

      0

      c2 audit mode (A, RR)

      0

      1

      0

      clr enabled

      0

      1

      0

      common criteria compliance enabled (A, RR)

      0

      1

      0

      cost threshold for parallelism (A)

      0

      32767

      5

      cross db ownership chaining

      0

      1

      0

      cursor threshold (A)

      -1

      2147483647

      -1

      Database Mail XPs (A)

      0

      1

      0

      default full-text language (A)

      0

      2147483647

      1033

      default language

      0

      9999

      0

      default trace enabled (A)

      0

      1

      1

      disallow results from triggers (A)

      0

      1

      0

      fill factor (A, RR)

      0

      100

      0

      ft crawl bandwidth (max), see ft crawl bandwidth(A)

      0

      32767

      100

      ft crawl bandwidth (min), see ft crawl bandwidth(A)

      0

      32767

      0

      ft notify bandwidth (max), see ft notify bandwidth(A)

      0

      32767

      100

      ft notify bandwidth (min), see ft notify bandwidth(A)

      0

      32767

      0

      index create memory (A, SC)

      704

      2147483647

      0

      in-doubt xact resolution (A)

      0

      2

      0

      lightweight pooling (A, RR)

      0

      1

      0

      locks (A, RR, SC)

      5000

      2147483647

      0

      max degree of parallelism (A)

      0

      64

      0

      max full-text crawl range (A)

      0

      256

      4

      max server memory (A, SC)

      16

      2147483647

      2147483647

      max text repl size

      0

      2147483647

      65536

      max worker threads (A, RR)

      128

      32767

      (1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.)

      0

      Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256+(<processors> -4) * 8) for 32-bit SQL Server and twice that for 64-bit SQL Server.

      media retention (A, RR)

      0

      365

      0

      min memory per query (A)

      512

      2147483647

      1024

      min server memory (A, SC)

      0

      2147483647

      8

      nested triggers

      0

      1

      1

      network packet size (A)

      512

      32767

      4096

      Ole Automation Procedures (A)

      0

      1

      0

      open objects (A, RR, obsolete)

      0

      2147483647

      0

      PH_timeout (A)

      1

      3600

      60

      precompute rank (A)

      0

      1

      0

      priority boost (A, RR)

      0

      1

      0

      query governor cost limit (A)

      0

      2147483647

      0

      query wait (A)

      -1

      2147483647

      -1

      recovery interval (A, SC)

      0

      32767

      0

      remote access (RR)

      0

      1

      1

      remote admin connections

      0

      1

      0

      remote login timeout

      0

      2147483647

      20

      remote proc trans

      0

      1

      0

      remote query timeout

      0

      2147483647

      600

      Replication XPs Option (A)

      0

      1

      0

      scan for startup procs (A, RR)

      0

      1

      0

      server trigger recursion

      0

      1

      1

      set working set size (A, RR, obsolete)

      0

      1

      0

      show advanced options

      0

      1

      0

      SMO and DMO XPs (A)

      0

      1

      1

      SQL Mail XPs (A)

      0

      1

      0

      transform noise words (A)

      0

      1

      0

      two digit year cutoff (A)

      1753

      9999

      2049

      user connections (A, RR, SC)

      0

      32767

      0

      User Instance Timeout (A, only appears in SQL Server 2005 Express Edition)

      5

      65535

      60

      user instances enabled (A, only appears in SQL Server 2005 Express Edition)

      0

      1

      0

      user options

      0

      32767

      0

      Web Assistant Procedures (A)

      0

      1

      0

      xp_cmdshell (A)

    segunda-feira, 25 de junho de 2007 13:26
    Moderador
  • Excelente dica, ela funciona também no SQL2008 E 2008R2?
    sexta-feira, 1 de outubro de 2010 15:41
  • Coimbra,

    Sim da mesma forma.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    sábado, 2 de outubro de 2010 00:13
    Moderador