How to Batch Control xp_cmdshell function on Multiple SQL Servers

xp_cmdshell is a handy function and allows SQL Server database administrators to complete tasks quickly and get transformed as a proactive and efficient database administrator. However, it also allows the user to run windows programs using the SQL Server’s service account. In this article, you will learn how to control the status of xp_cmdshell from your Central Server.

On a single server

To check the status of xp_cmdshell on a single SQL Server instance, you can run this command from a new query window. If the value is 1, xp_cmdshell is enabled on that SQL Server instance. If the value is 0, xp_cmdshell is disabled.

SELECT * FROM SYS.CONFIGURATIONS WHERE Name = 'xp_cmdshell'

If the xp_cmdshell is enabled, disable it using this command.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

Many DBAs might enable xp_cmdshell for automating some tasks and do not turn it off. So, it’s a good practice to track the status of xp_cmdshell and disable it if it was enabled.

Let us automate it

Batch Control xp_cmdshell function On Multiple SQL ServersYou should be setting this automation from a Central Server. Create linked server for all target servers and then store server names in a table @Tserver. Through cursor the script would fetch each server name and then disable the xp_cmdshell on it. Please note, the script is not checking the status of xp_cmdshell. It directly disables it. If you want to enable the script to check the status of xp_cmdshell and then disable it, then you should use sys.configurations inside the script.

declare @Tserver table
(cserver varchar(200))
insert into @Tserver values ('Server1')
declare C1 cursor for
select * from @Tserver
declare @cmd nvarchar(2000), @server varchar(200)
open C1
fetch next from C1 into @server
while @@FETCH_STATUS = 0 
begin
set @cmd = N'EXEC (''EXEC sp_configure ''''show advanced options'''', 1
RECONFIGURE
EXEC sp_configure ''''xp_cmdshell'''', 0
RECONFIGURE'') at [' + @server + ']'
print @cmd
exec (@cmd)
fetch next from C1 into @server
end 
close C1
deallocate C1

Tweak it

Enable xp-cmdshellIn some servers, it might be necessary that the xp_cmdshell is enabled. In such cases, you can create another reference table, store all server names that require xp_cmdshell in this table. When fetching server names from the first table i.e., @Tserver, you should also check this table and if the server name is in the reference table, then skip it. You can also enable xp_cmdshell for a short period. To do that, create a table and add 2 columns. The first column should be server name and the second column should be the date when xp_cmdshell is disabled. The script should be made to refer this table and if the deadline has been reached, xp_cmdshell should be disabled on the server. Many DBAs recommend to turn off xp_cmdshell . That is as part of security measures. However using xp_cmdshell will never cause SQL Server corruption.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook fix and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.