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
BEGIN
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
END

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

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

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

Trackbacks/Pingbacks

  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 *