Limit Return of SQL Query against Active Direct groups to specific Organisation Unit

Question

I have a query to get all the groups that an AD person is in; but I have not been able to limit the results to only chosen Organisation Units, which I would like to do. Here is the query I have:

-- Test Variable --
DECLARE @Username NVARCHAR(254) = 'ABC'


DECLARE
     @DomainController  NVARCHAR(50)    = N'DC=DOMAIN,DC=COM'
    ,@Fields            NVARCHAR(256)   = N'ADsPath,cn'
    ,@ParamDefn         NVARCHAR(50)    = N'@PathOut NVARCHAR(1024) OUTPUT'

DECLARE
     @SQL   NVARCHAR(4000)
    ,@Path  NVARCHAR(1024)

SET @SQL =
'SELECT @PathOut = distinguishedName
FROM OPENQUERY(ADSI, ''
SELECT distinguishedName
FROM ''''LDAP://' + @DomainController + '''''
WHERE 
    objectClass = ''''user''''
AND sAMAccountName = ''''' + @Username + ''''''')'

EXEC sp_executesql @SQL, @ParamDefn, @PathOut = @Path OUTPUT

SET @SQL =
'SELECT *
FROM OPENQUERY (ADSI, ''<LDAP://' + @DomainController + '>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path +'));' + @Fields + ';subtree'')'

EXEC SP_EXECUTESQL @SQL

This returns all the groups of which @Username is a member and it's great. However, the results come back with the ADsPath column looking something like this:

LDAP://CN=Group1,OU=Role Groups,OU=Groups,DC=DOMAIN,DC=COM
LDAP://CN=Group2,OU=Application Groups,OU=Groups,DC=DOMAIN,DC=COM
LDAP://CN=Group3,OU=Role Groups,OU=Groups,DC=DOMAIN,DC=COM
LDAP://CN=Group4,OU=File System Groups,OU=Groups,DC=DOMAIN,DC=COM

My question is this: how can I change my query so that (for instance) only groups where OU=Role Groups are returned?

The only way I can see to succeed at the moment is to examine the contents of the ADsPath after the event, using string functions and I do not want to go that way if I can avoid it, as it seems inefficient to get all groups and then get a subset of that result (not to mention the nastiness of the solution in other ways).

Answer 1

Perhaps I'm missing something, but the most straightforward way to limit a query to a specific OU to add another filter to the ADSI query.

If you want to use LDAP dialect for ADSI, you can add, for example, (distinguishedName=*,OU=Role Groups,OU=Groups,DC=DOMAIN,DC=COM) to your ADSI filter.

If you want to use SQL dialect for ADSI, you can add AND distinguishedName = '*,OU=Role Groups,OU=Groups,DC=DOMAIN,DC=COM' to your WHERE clause for the ADSI query.