Urgent!! Active Directory VBscript to add a user to many groups.!!!!

I have a script at the moment which will add users, update user attributes, move user account to a different OU and update the changed information.  All this is using information in a SQL table [Table A]. Fields used are things like Dept, job title,site, login , etc.
The Dept,job title, and site fields are used to determine certain group membership.  These will be referenced in a second table [table B].  


What I need to happen is that I want the script to determine which groups a user is a member of, then check to see which groups the user should be a member of and then remove or add the user to these groups if need, if not needed it should remove them.

I need this to first check security groups and then to check distributiion groups.  These are stored in the same way.

Could somebody please help me with this. I will give this the maximum amount of points I can but will increase these if the answer helps.
WNottsCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris DentPowerShell DeveloperCommented:

Groups are listed as an attribute of the User Object in AD. This would echo out the distinct names of the groups.

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objUser = GetObject("LDAP://CN=Test User,CN=Users," & objRootDSE.GetObject("defaultNamingContext"))

For Each strGroupDN in objUser.GetEx("memberOf")
      Set objGroup = GetObject("LDAP://" & strGroupDN)
      WScript.Echo objGroup.Get("name")
Next

In the example above GetEx is used instead of Get for picking up the Group Membership. This is because GetEx always returns the attribute as an Array even if it's just a 1 element array.

Checking the Group Type is also pretty easy (if you want to distinguish between Security and Distribution). You need to read the Group Type property then an And operation - continuing on with the above example:

ADS_GROUP_TYPE_GLOBAL_GROUP = &H2
ADS_GROUP_TYPE_DOMAIN_LOCAL_GROUP = &H4
ADS_GROUP_TYPE_LOCAL_GROUP = &H4
ADS_GROUP_TYPE_DOMAIN_LOCAL_GROUP = &H4
ADS_GROUP_TYPE_UNIVERSAL_GROUP = &H8
ADS_GROUP_TYPE_SECURITY_ENABLED = &H80000000

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objUser = GetObject("LDAP://CN=Test User,CN=Users," & objRootDSE.GetObject("defaultNamingContext"))

For Each strGroupDN in objUser.GetEx("memberOf")
      Set objGroup = GetObject("LDAP://" & strGroupDN)
      intGroupType = objGroup.Get("groupType")
      If intGroupType And ADS_GROUP_TYPE_SECURITY_ENABLED Then
            WScript.Echo objGroup.Get("name") & ": Security Group"
      Else
            WScript.Echo objGroup.Get("name") & ": Distribution Group"
      End If
Next

To remove a member from a Group you do:

objGroup.Remove objUser.ADSPath

Or

objGroup.Remove "LDAP://" & objUser.Get("distinguishedName")

Hope that helps so far.

Chris
0
WNottsCAuthor Commented:
Thanks for that it sounds the sort of thing I want to do.  However I could do with a total solution for the following situation.  If more tan 500 points are needed I can split this into two question.

1) I have a SQL table, tableA, that holds all of the users that need to be created or updated.
    The fields in this table are LOGIN, dept, site, jobtitle, etc
2) I have a second SQL table, tableB, wich holds the security or distribution groups.
    The fields in this table are dept, site, jobtitle, security/distribution, group
     E.g. dept1, site1, job1, security, staffdept1
            dept1, site1, job1, security, CIS
            dept1, site1, job1, distribution, CIS
            dept2, site1, job1, security, staffdept2
3) When the user is created which I  have a script to do this(provided by you already) I need it to set the security and distribution groups up as referenced in tableB for this user.
4) if the user is already created and in the correct OU then check the user has the correct security if not add or remove where appropriate(this may just be add since they should not need any removing, if this makes it easier)
5) if the user is already created but in the wrong OU we move the user I have a script to do this (provided by you already). The user will be in the wrong groups so remove all groups and then add the correct from tableB

I know there is alot here to do but any advice on an alternative way to do this would be great.

Thanks
0
Chris DentPowerShell DeveloperCommented:

UpdateUser would be the best place to have the group membership alterations, it could be set after the MoveUser call has been made to ensure the user is in the right place before anything is changed.

The only bit that I'm not sure on is how to get the list of Groups for the user out of the database. How are they linked to the username (which is pretty much the primary key for me)?

Chris
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

WNottsCAuthor Commented:
the user tableA would for a user have the site, department, job-title.  tableB would hold the security groups for the site, department, job-title.

e.g.
Table A
login         site     dept  job-title
WIL5278    DR     CIS    database administrator

Table B
site     dept  job-title                         Security type     Group
DR     CIS    database administrator    Security           Staff
DR     CIS    database administrator    Security           CIS
DR     CIS    database administrator    Distribution       DR

Does this help?

0
Chris DentPowerShell DeveloperCommented:

hehe not really... but then I'm really not very good at databases, never bothered learning how really.

Basically I need to be able to extract a list of groups from the database (group name, type doesn't matter) for a specific user, how would that happen?

Once we have that it can be loaded into a dictionary object and compared with what's there. If a group isn't there and should be it can add it, if it is there and shouldn't be then it can be removed.

Chris
0
WNottsCAuthor Commented:
With the listof groups from the database would it be for each group for that user you would go through a loop adding or removing the security and then move to the next group?

ok say I  can retrieve a list of groups for you what would the rest of the code be?
0
Chris DentPowerShell DeveloperCommented:

Is the list of groups from the database going to be user specific? As long as there's a way to tell which groups the user should be in then it's easy.

Name Translate is at the bottom again, I think we have the bits in place for it, but it requires a few constants and objNetwork:

Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_1779 = 1
Const ADS_NAME_TYPE_NT4 = 3

Set objNetwork = CreateObject("WScript.Network")

If those are already there then something like this could deal with the groups:

' Load groups from the database into a Dictionary - User specific list (how it does that is to be determined):

Set objGroups = CreateObject("Scripting.Dictionary")
For Each <Group From the Database>
      If Not objGroups.Exists(strGroup) Then
            objGroup.Add strGroup
      End If
Next

For Each strGroupDN in objUser.GetEx("memberOf")
      Set objGroup = GetObject("LDAP://" & strGroupDN)

      ' If the group isn't in the list of groups for the user then remove it
      ' Then remove the Group from the users list so we end up with a distinct list of groups the user should be but
      ' isn't a member of. This needs testing to ensure that the entries in the database match up with the group names.

      If Not objGroups.Exists(objGroup.Get("name")) Then
            objGroup.Remove objUser.ADSPath
            objGroups.Remove objGroup.Get("name")
      End If
Next

' What's left in the objGroups list are the groups the user should be a member of. Need to use NameTranlate to get the
' Group DN from the name:

Set objNameTranslate = CreateObject("NameTranslate")

If objGroups.Count > 0 Then
      For Each strGroupName in objGroups
            strGroupName = objNetwork.UserDomain & "\" & strGroupName
            objNameTranslate.Init ADS_NAME_INITTYPE_GC, ""
            objNameTranslate.Set ADS_NAME_TYPE_NT4, strGroupName
            If Err.Number <> 0 Then
                  ' Couldn't find Group
            Else
                  strGroupDN = objNameTranslate.Get(ADS_NAME_TYPE_1779)
                  Set objGroup = GetObject("LDAP://" & strGroupDN)
                  objGroup.Add objUser.ADSPath
            End If
      Next
End If

Set objNameTranslate = Nothing
0
WNottsCAuthor Commented:
To answer your question I would use something like the following:-

' Connection string.
      strCon = "DSN=CIS...Q UID=myUser;Password=myPassword"

      ' Create the required ADO objects.
      Set conn = CreateObject("ADODB.Connection")
      Set rs = CreateObject("ADODB.recordset")

      ' Open the connection.
      conn.Open strCon

      ' Retrieve some records.
      strSQL = "Select * from TableB where Dept =" & strDept & ",Site = " &
                strSite & ",Job-title = " & strJobTitle
      rs.CursorLocation = adUseClient
      rs.Open strSQL, conn, adOpenStatic, adLockOptimistic

               Do While Not rs.EOF
               
               With rs
                         strsecurity = Trim("" & .Fields("group"))
               End With

              rs.MoveNext
              Loop
0
Chris DentPowerShell DeveloperCommented:

Cool that looks fine.

In which case we (hopefully) have:

' Load groups from the database into a Dictionary - User specific list:

Set objGroups = CreateObject("Scripting.Dictionary")

' Connection string.
strCon = "DSN=CIS...Q UID=myUser;Password=myPassword"

' Create the required ADO objects.
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.recordset")

' Open the connection.
conn.Open strCon

' Retrieve some records.
strSQL = "Select * from TableB where Dept =" & strDept & ",Site = " &_
      strSite & ",Job-title = " & strJobTitle

rs.CursorLocation = adUseClient
rs.Open strSQL, conn, adOpenStatic, adLockOptimistic

Do While Not rs.EOF
      With rs
            strGroup = Trim("" & .Fields("group"))
      End With

      If Not objGroups.Exists(strGroup) Then
            objGroup.Add strGroup
      End If
      rs.MoveNext
Loop
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2003

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.