Received an interesting question recently from myITForum regular Joshua Searles. Was it possible to use SQL Server stored procedure to directly add computer names in a collection to a security group in Active Directory? I advised that Josh could take advantage of the SQL Server extended stored procedure xp_CmdShell to enumerate the collection, then pass the computer name to a VBScript, PowerShell or possibly the NET GROUP command using xp_CmdShell.
Josh ran with those ideas and created the following batch solution, which could be adapted to a stored procedure.
(in his own words)
Basically I have a collection of systems that are on slow wan links that I want to add to a Security Group that applies a “BITS Throttling Policy”.
I’ll run it as a stored procedure, so that we’re sure to apply the policy to any system that gets deployed to those subnets. Next will be a script to remove the machine from the group, when it is no longer a member of the collection.
‘************************************************************************************
create table #tmp_name_list (HostNames varchar(20))
insert into #tmp_name_list
SELECT Name FROM dbo.v_CM_RES_COLL_SMS00001
SELECT * FROM #tmp_name_list
DECLARE @host_name sysname
DECLARE host_list cursor
for select HostNames
FROM #tmp_name_list
for read only
declare @COMMAND sysname
open host_list
fetch next from host_list into @host_name
while @@fetch_status=0
begin
SET @COMMAND = 'NET GROUP "BITS Throttling" ' + @host_name + '$'+' /ADD /DOMAIN'
exec.master.dbo.xp_cmdshell @COMMAND , NO_OUTPUT
fetch next from host_list into @host_name
PRINT @COMMAND
end
close host_list
deallocate host_list
drop table #tmp_name_list
‘*****************************************************************
ST Note: Be aware that there are known security concerns with xp_CmdShell, pay particular attention to the account that you give “Exec” permissions to this command. More can be found here: http://msdn.microsoft.com/en-us/library/aa175398(SQL.80).aspx