tr3vayn3
09-05-2011, 04:39 AM
Hi everyone,
Im quite new to this forum, found it on my search to address my problem with a particular Macro.
Anyways, what I am trying to achieve is to pull out the list of Security groups a User is a Member Of. I came across this VBA (see below), but I could not entirely get it to work.
Our AD structure has an OU called "Enteng Users" this is were we place all our LANID's. Apart from this we also have the standard OU "Users" which we do not actually use.
I have been trying to get the script below to work, basically it is querying the LAN ID (from Excel sheet Macro) on Cell D2 and returns the value on Cell A8.
During my test, it could not return any result for LANID's on the "Enteng Users" OU while it does return results for LANID's on the "Users" OU.
I hope someone could have a look at the script and help me identify which is which and what I can do to make it work.
I am quite desperate as I have been trying to figure this out since last week but with no luck.
Thank you very much in advance and hope to hear from you.
Regards,
tr3vayn3
Script:
Option Explicit
Sub GetGroupData()
GetGroupMembership Sheet1.Range("D2").Value, Sheet1.Range("A8")
End Sub
Sub GetGroupMembership(strUser As String, rngOut As Range)
Dim objConnection, objCommand, objRecordSet, objUser, objRootDSE, objMember
Dim strLine, arrGroup
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")
objCommand.CommandText = "SELECT aDSPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext") & _
"' WHERE objectClass='user' And name='" & strUser & "'"
Set objRootDSE = Nothing
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute
rngOut.CurrentRegion.Offset(1).ClearContents
While Not objRecordSet.EOF
Set objUser = GetObject(objRecordSet.Fields("aDSPath"))
For Each objMember In objUser.GetEx("memberOf")
strLine = Mid(objMember, 4, 330)
arrGroup = Split(strLine, ",")
rngOut.Value = arrGroup(0)
Set rngOut = rngOut.Offset(1)
Next
' Additional section to find the primary group.
If objUser.primaryGroupID = 513 Then
rngOut.Value = "Domain Users"
Else
If objUser.primaryGroupID = 515 Then
rngOut.Value = "Domain Computers"
Else
rngOut.Value = "Maybe a Domain Controller"
End If
End If
Set objUser = Nothing
objRecordSet.MoveNext
Wend
objConnection.Close
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Sub
Im quite new to this forum, found it on my search to address my problem with a particular Macro.
Anyways, what I am trying to achieve is to pull out the list of Security groups a User is a Member Of. I came across this VBA (see below), but I could not entirely get it to work.
Our AD structure has an OU called "Enteng Users" this is were we place all our LANID's. Apart from this we also have the standard OU "Users" which we do not actually use.
I have been trying to get the script below to work, basically it is querying the LAN ID (from Excel sheet Macro) on Cell D2 and returns the value on Cell A8.
During my test, it could not return any result for LANID's on the "Enteng Users" OU while it does return results for LANID's on the "Users" OU.
I hope someone could have a look at the script and help me identify which is which and what I can do to make it work.
I am quite desperate as I have been trying to figure this out since last week but with no luck.
Thank you very much in advance and hope to hear from you.
Regards,
tr3vayn3
Script:
Option Explicit
Sub GetGroupData()
GetGroupMembership Sheet1.Range("D2").Value, Sheet1.Range("A8")
End Sub
Sub GetGroupMembership(strUser As String, rngOut As Range)
Dim objConnection, objCommand, objRecordSet, objUser, objRootDSE, objMember
Dim strLine, arrGroup
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")
objCommand.CommandText = "SELECT aDSPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext") & _
"' WHERE objectClass='user' And name='" & strUser & "'"
Set objRootDSE = Nothing
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute
rngOut.CurrentRegion.Offset(1).ClearContents
While Not objRecordSet.EOF
Set objUser = GetObject(objRecordSet.Fields("aDSPath"))
For Each objMember In objUser.GetEx("memberOf")
strLine = Mid(objMember, 4, 330)
arrGroup = Split(strLine, ",")
rngOut.Value = arrGroup(0)
Set rngOut = rngOut.Offset(1)
Next
' Additional section to find the primary group.
If objUser.primaryGroupID = 513 Then
rngOut.Value = "Domain Users"
Else
If objUser.primaryGroupID = 515 Then
rngOut.Value = "Domain Computers"
Else
rngOut.Value = "Maybe a Domain Controller"
End If
End If
Set objUser = Nothing
objRecordSet.MoveNext
Wend
objConnection.Close
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Sub