Temporarily Enabling xp_cmdshell

I use xp_cmdshell quite a bit in my envrionment. Sometimes I use it from within a script to call BCP (to export), XCOPY, or sometimes even SQLCMD. At the same time, we don’t like to keep xp_cmdshell enabled unless we’re actually using it. So I’ve come up with a little bit of reusable code. If I need to use xp_cmshell in a script, I first check to see if it’s enabled. If it’s already enabled it, I don’t want to disable it because some instances actually need. But if it’s not enabled, I want to temporarily enable it.

DECLARE @v_cmdshell int

-- Determine if xp_cmdshell is currently enabled. If not, we will have to temporarily enable it.
SELECT @v_cmdshell = state
FROM master.sys.system_components_surface_area_configuration
WHERE object_name = 'xp_cmdshell'

-- If xp_cmdshell isn't enabled, we will need to temporarily enable it now.
IF @v_cmdshell = 0
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1

-- This is the part of the script where we do stuff.

-- If xp_cmdshell was disabled, turn it back off.
IF @v_cmdshell = 0
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 0

8 Responses to “ “Temporarily Enabling xp_cmdshell”

  1. Attila says:

    I started rinedag your article and realized I had some preconceived ideas about this topic. Your compelling views made me rethink myself. In this case it was a good thing.

  2. Regev says:

    Hello Mr. Mike Hillwig and Thank you for the wonderful idea!
    If I want the whole process to run dynamically, meaning that at the end of the process XP_CMD returns to its existing, how should I plan the program?

  3. I’ve been looking for a post like this for an age

  4. Happy Birthday Mr. C~~~ you young, handsome, PATIENT man! ( and no, I have never met him, I’m just assuming)Mrs. C~~~ If you took almost 200 shots, I say, send them all out! To everyone. I mean, how much could stamps cost?If not, my personal fav is #1.

  5. Hay que aplicar el concepto de Low Cost a los gobiernos y todos sus empleados, desde los cargos políticos hasta los más humildes funcionarios públicos. Porque lamentablemente los países que ahora hacen ajustes, los hacen mal. Combinan reducciones del gasto con aumentos de impuestos; y si hay algo que no debe hacer una economía recesiva es incrementar los impuestos. La respuesta es reducir el estado (clase improductiva) a una mínima fracción.Vota el comentario: 3  2

  6. To call most atheists "unbelievers" is to do them a dis-service. For many of them, their belief in an unprovable nothing is as fervent and unshakable as a fundamentalist Christian's belief in an unprovable, dinosaur bone hiding Almighty.And, as you note, they are often just as committed to sharing those beliefs at high volume, and converting the non-believer as any evangelical.I've always maintained that agnosticism is the only intellectually honest result of a failure to be convinced by any existing theology.

  7. It was Very good post, I was really searching for this topic, as I wanted this topic to understand completely and it is also very rare in internet, that is why it was very difficult to understand.Thank you for sharing this.regards:

  8. Londres é um delícia! Estou por aqui fazendo um curso, e a verdade é que não dá vontade de ir embora. Earl’s Court de fato é um dos melhor lugares para se ficar perto. Já eu cá estou em Fulham! Muitos bons passeios! Beijokas!


  1. Log Buffer #291, A Carnival of the Vanities for DBAs | The Pythian Blog - [...] Mike Hillwig uses xp_cmdshell quite a bit in his environment and shares the experience. [...]
  2. Michael hillwig | Kickthathabit - [...] Temporarily Enabling xp_cmdshell | Cranky DBAPosted by Mike Hillwig. October 17, 2012. I use xp_cmdshell quite a bit in…

Leave a Reply

Your email address will not be published. Required fields are marked *