Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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.
0
WNottsC
Asked:
WNottsC
  • 5
  • 4
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now