Share This Post

How to add SMS 2003/ConfigMgr 2007 computers in a collection to an Active Directory security group

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

Share This Post

Specializing in System Center Products: Configuration Manager 2007, Operations Manager 2007, Desired Configuration Management, SMS 2003, software distribution, ITMU, SUS & WSUS. Database products and solutions: Microsoft SQL Server 2005 & 2008, Business Intelligence Development Studio, Analysis Server, Reporting Services, Integration Services. Microsoft Windows Server 2003 & 2008, SharePoint, PerformancePoint, Active Directory, DNS, Group Policy, Security, VBScript, ASP, Visual Basic, WMI, HTML and XML.

Leave a Reply