Finding All Users in an Active Directory Group

Fun with ADSI

March 26, 2014

I am a big believer in using the ADSI query provider for being able to conduct LDAP queries in SQL Server against Active Directory. You set up an ADSI linked server in your SQL Server (I usually call mine, well, “ADSI”) and then use Transact-SQL’s OPENQUERY to pass off a query to A/D. You can filter the results in the LDAP query, but you can then also use all sorts of SQL chewy goodness against the result set, such as grouping, ordering, more filtering, etc. All good.

Today I had to solve a specific problem – finding all users in an A/D group, when that group may contain groups, and those groups may contain groups, and so on. What I want at the end is a list of all end users that are in the “root” group, “exploding” all of the child groups so that I get nothing but users at the end, not intervening child groups.

Consider the following example:

If I query against the group “Apps” I want to have the following returned:

I do not want either the CRMApp nor AccountingApp group names in the results.

The rest of this post will describe the LDAP syntax to use as well as a few tips I often use to filter out “real people” in LDAP query results from service accounts, elevated ids, contacts, and the like. Here is the sample query:

SELECT
        *
FROM OPENQUERY(ADSI,
        '<LDAP://DC=FOO,DC=COM>;
        (&(objectCategory=Person)(objectClass=user)(sn=*)(givenName=*)(title=*)(ipPhone=*)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(memberof:1.2.840.113556.1.4.1941:=cn=Apps,ou=Groups,dc=FOO,dc=COM));
        cn;subtree')

The OPENQUERY query syntax for LDAP via ADSI is as follows:

Note: Each of the above query parts are separated from the others by semicolons. You can place the independent parts on different lines as shown above, but you cannot split a given part into multiple lines. In other words, the search filter must be all on one line, as shown.

Let’s pull the query apart piece by piece:

  1. The query starts at <LDAP://DC=FOO,DC=COM> and looks recursively down from there (because of the subtree at the end of the query). For performance or security purposes you could change the starting point of the query if you knew it was rooted somewhere deeper in your directory structure.
  2. It looks for for directory entries that:
    • Are a personobjectCategory=Person
    • Are a user (not a contact) – objectClass=user
    • Have a last name (surname, or sn) – sn=*
    • Have a first name (given name or givenName) - givenName=*
    • Have a job title (requires you to fill this in Active Directory, obviously) – title=*
    • Have a desk phone (in our case filled in by our VOIP software) – ipPhone=*
    • Are NOT disabled accounts (note this is basically a “magic number”) – !(userAccountControl:1.2.840.113556.1.4.803:=2)
    • Belong to the group (I figure out the group’s common name or “cn” first, if you prepend that with the memberof:1.2.840.113556.1.4.1941:= magic string that is what does the recursive lookup through all its member groups and users to find all its members) - memberof:1.2.840.113556.1.4.1941:=cn=Apps,ou=Groups,dc=FOO,dc=COM

At this time the sample query only returns common names (cn), but could return user ids and anything else for that matter just by adding the comma-delimited attributes to the list along with cn at the end of the query. Some common attributes I often include in my results are:

A few final notes:

That’s it. ADSI queries in general are a very common technique I use for various purposes, and this example was a real-world example that came up today. I hope you found it helpful.

For more of my thoughts on LDAP, see this post.