Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I add a user to a group, but only if that user is a member of the IT department?

-- JV

SpacerHey, Scripting Guy! AnswerScript Center

Hey, JV. You know, this is your lucky day: not only are we going to show you how you can add a specified user to a group (assuming, of course, that this user is a member of the IT department), but we’re also going to show you a way to automatically add all members of the IT department to a group. Talk about a deal, huh?

And, no, your thanks are enough for us. Well, that is, your thanks and our standard consulting fee of $12,343.50. We’ll send you a bill.

So what do you get in return for that $12,343.50? (And no, you don’t have to send us $12,343.50. Not unless you want to ….) Well, for starters, you get a script like this one, which adds the user Jack Richins to the group IT Staff … provided, of course, that Jack is a member of the IT department:

Set objUser = GetObject("LDAP://cn=Jack Richins,ou=canada,dc=fabrikam,dc=com")

If objUser.Department = "IT" Then
    Set objGroup = GetObject _
        ("LDAP://cn=IT Staff,ou=support,dc=fabrikam,dc=com")
    objGroup.Add(objUser.ADsPath)
End If

As you can see, this is a simple little script. We begin by using this line of code to bind directly to Jack Richins’ user account in Active Directory:

Set objUser = GetObject("LDAP://cn=Jack Richins,ou=canada,dc=fabrikam,dc=com")

We then check to see whether or not Jack’s Department attribute is equal to IT:

If objUser.Department = "IT" Then

Let’s assume that it is. In that case, we then create a second object reference, one that connects us to the IT Staff group account:

Set objGroup = GetObject _
    ("LDAP://cn=IT Staff,ou=support,dc=fabrikam,dc=com")

Once we’ve made that connection we can then call the Add method (passing the value of Jack’s ADsPath attribute as the sole parameter) and add Jack to the group. If Jack isn’t part of the IT department then we don’t do anything at all.

Not bad, huh? Now here’s the bonus script. This script searches Active Directory and returns a list of all the users (objectCategory='user') who happen to be members of the IT department (Department=’IT’). For each user meeting those criteria (that is, each user in the IT department), the script adds the user to the IT Staff group:

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

objCommand.CommandText = _
    "SELECT ADsPath FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user' " & _
         "AND Department='IT'"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst

Set objGroup = GetObject _
    ("LDAP://cn=IT Staff,ou=support,dc=fabrikam,dc=com")

Do Until objRecordSet.EOF
    objGroup.Add(objRecordSet.Fields("ADsPath").Value)
    objRecordSet.MoveNext
Loop

We’re not going to talk about the bonus script in any detail; if you aren’t sure how Active Directory scripts work we recommend you take a peek at the two-part Tales from the Script series Dude, Where’s My Printer? About all we will do is mention that the On Error Resume Next statement is very important in this particular script. Why? Well, suppose Jack Richins is already a member of the IT Staff group and you try adding him (again) to the group. That’s going to generate an error and the script will blow up. If you add the On Error Resume Next statement, however, the script won’t blow up; instead, it will simply skip Jack and instead try adding the next user in the IT department.

Note. If you’re wondering how the Scripting Guys came up with a standard consulting fee of $12,343.50, well, it’s purely coincidental that one of the Scripting Guys (with great reluctance) recently purchased a car for his son and the total bill came to $12,343.50. Like we said, purely coincidental ….