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

No Responses to “ “Temporarily Enabling xp_cmdshell”

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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>