'========================================================================== ' ' NAME: GetRecursiveGroupMembership.vbs ' ' AUTHOR: Ryan J. Adams ' DATE : 12/20/2007 ' DATE : 12/9/2012 This was updated to look for accounts running SQL Server instead of just an input account ' ' COMMENT: This script was designed to see if the service account running SQL Server is an administrator on the server. ' It finds the service accounts for all SQL Server Services (instances) and grabs all groups they are a member of. ' It recursively finds every group that those groups are members of. ' Last it finds all the members of the local administrators group and compares the lists. We simply write out any matches. ' '========================================================================== On Error Resume Next strComputer = "." 'You can put any computer name here. The period denotes the local machine. FQDN over NetBIOS is always suggested. arrAllMembers = Array() ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' This sub creates a GC connection to AD if you are querying all of AD. If you just want a specific ' OU then just create a constant for the path and remove the first commented section that retrieves ' the GC path. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Const ADS_SCOPE_SUBTREE = 2 'Find the Global Catalog server Set oCont = GetObject("GC:") For Each oGC In oCont strADsPath = oGC.ADsPath Next 'Here we create the connection object and the command object. 'Then we set the provider value for the connection object. 'Lastly we open the connection. Set objConnection = CreateObject("ADODB.Connection") Set objCommand = CreateObject("ADODB.Command") objConnection.Provider = "ADsDSOObject" objConnection.Open "Active Directory Provider" 'Here we set the command object's active connection equal to the connection object that 'we created above. We also set the page size property for the command object so that 'when we execute the command it is does not truncate the data at the default AD page size. Set objCommand.ActiveConnection = objConnection objCommand.Properties("Page Size") = 1000 objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") Set colServices = objWMIService.ExecQuery("Select * from Win32_Service where name like 'MSSQLS%'") x=0 For Each objService in colServices strUserName = Mid(objService.StartName,InStr(objService.StartName,"\")+1) 'Here we strip off the domain name to get the SamAccountName ReDim Preserve arrAllMembers(x) arrAllMembers(x) = strUserName x=x+1 strUserPath = GetPath(strUserName) 'This sends the SamAccountName to a function to get back the full LDAP path Set objUser = GetObject("LDAP://" & strUserPath) 'This creates an object to manipulate the user account Set colGroups = objUser.Groups 'This gets the groups the user is a member of. For Each objGroup in colGroups GetNextGroup(objGroup) 'This sends each group to a recursion function Next Next Set objGroup = GetObject("WinNT://" & strComputer & "/Administrators,group") 'Here we grab the local Administrators group For Each objUser in objGroup.Members 'Here we walk through everything in local administrators group For y=0 to UBound(arrAllMembers) 'Here we walk through the array of groups the account is a member of and compare it to the members of the local administrators group If objUser.Name = arrAllMembers(y) Then Wscript.Echo arrAllMembers(y) 'If we find a match we spit it out End If Next Next Function GetPath(strSamUserName) 'This function takes a SamAccountName and retrieves the full LDAP path, which we can then use to bind to an object objCommand.CommandText = _ "<" & strADsPath & ">;(&(objectCategory=User)(sAMAccountName=" & strSamUserName & "));distinguishedName;subtree" Set objRecordSet = objCommand.Execute objRecordSet.MoveFirst Do Until objRecordSet.EOF GetPath = objRecordSet.Fields("distinguishedName").Value objRecordSet.MoveNext Loop End Function Function GetNextGroup(objGroup) On Error Resume Next colMembers = objGroup.GetEx("memberOf") If Err.Number = 0 Then 'We get an error if the group has no members, so here we are only acting if the group has members For Each strMember in colMembers strPath = "LDAP://" & strMember Set objNestedGroup = GetObject(strPath) ReDim Preserve arrAllMembers(x) arrAllMembers(x) = objNestedGroup.CN x=x+1 GetNextGroup(objNestedGroup) 'This is the recursion point where we call the same function again to see if that group also has more members and so on Next Else Err.Clear 'We have to clear the error for the iteration End If End Function