RSS Feed

Temporarily Enabling xp_cmdshell

2

October 17, 2012 by Mike Hillwig

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.

[SQL]

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[/SQL]


Search

Pages