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