SQL Query AD Distribution Lists Membership

Ever wanted to query your AD from SQL and then list all the members of your Distribution Lists?

Because you can’t do it, sheesh, that was easy.

There are some limitations in OPENQUERY which means you cannot query multi-value fields from SQL, but you can pull results from a specific Distribution lists.

Well funny you should end up here then, because I have just the thing.

Something like this would work to get members of a DL

SELECT name FROM OPENQUERY( ADSI,'SELECT name, memberof
FROM ''LDAP://ADserver.domain''
WHERE memberOf=''CN=of the dl you want'' ')
Something like this will list all the DLs
SELECT DISTINCT distinguishedName,name
FROM OPENQUERY(ADSI,'SELECT distinguishedName,name
FROM ''LDAP://ADserver.domain'' WHERE objectCategory=''group'' ')