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'' ')

Now you say “Aha”

Firstly Assumptions:

You have created a linked server in SQL to your AD in order to query it

1. If not run this
   

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

You are familiar with Dynamic SQL

1.   If not go here http://www.sommarskog.se/dynamic_sql.html, this changed my life in SQL.

You have a split function

1. If not  http://stackoverflow.com/questions/2647/split-string-in-sql, thanks  vzczc

create function dbo.SplitString (@str nvarchar(4000),@separator char(1))
 returns table
 AS
 return (
 with tokens(p, a, b) AS (
 select 1, 1, charindex(@separator, @str) union all
 select p + 1, b + 1, charindex(@separator, @str, b + 1)
 from tokens where b > 0 )
 select p-1 zeroBasedOccurance, substring(@str, a, case when b > 0 then b-a ELSE 4000 end) AS s from tokens )
 GO

Now simply do the following: Copy and Paste this baby in Management Studio

/*-*-*-*-*-*-*-*-* ﷽‎ *-*-*-*-*-*-*-*-*-*-*-*/
/*Some Generic Declerations.. I can never remember which to use and which not to, so what is a couple of extra lines of kowd*/
DECLARE @DBName VARCHAR(200), @command nvarchar(4000), @SQL VARCHAR(4000), @i_Count INT, @a_Count INT, @MAX_Count INT, @DB_Name VARCHAR(500), @TableName VARCHAR(500), @SQL_Head VARCHAR(4000)
/*Settings Start*/DECLARE @RunMe INT, @DomNaim VARCHAR(50), @DebugMe INT, @DLName VARCHAR(500), @LDAP VARCHAR(500)
SET @DebugMe = 1 /*1 = Happy with code, attack mercilessly, 0 = Use Runme to step through manually and print EXECs*/
SET @DomNaim = ',DC=awesomeness,DC=com'
SET @LDAP = 'LDAP://ADserver.domain'
/*Settings End*/IF OBJECT_ID(N'tempdb..#dnTable', N'U') IS NOT NULL BEGIN DROP TABLE #dnTable END
IF OBJECT_ID(N'tempdb..#Output', N'U') IS NOT NULL BEGIN DROP TABLE #Output END
CREATE TABLE #dnTable (ID INT IDENTITY(1,1),distinguishedName VARCHAR(1000),name VARCHAR(1000))
CREATE TABLE #Output (DL VARCHAR(1000),name VARCHAR(1000))
SELECT @SQL = 'SELECT DISTINCT distinguishedName,name FROM OPENQUERY(ADSI,''SELECT distinguishedName,name
 FROM '''''+@LDAP+'''''
 WHERE objectCategory=''''group'''' '') AS derivedtbl_1 'SET @command = CONVERT(NVARCHAR(4000),@SQL)
 IF @DebugMe = 0 BEGIN PRINT @command END
 IF @DebugMe = 1
 BEGIN
 INSERT INTO #dnTable EXEC sp_executesql @command END
SET @MAX_Count = (SELECT COUNT(*) FROM #dnTable)
DECLARE @NGTable TABLE (n INT) INSERT INTO @NGTable exec NumberGenerator @MAX_Count
SET @i_Count = 1
WHILE @i_Count <= @MAX_Count
BEGIN SET @DLName = (SELECT distinguishedName FROM #dnTable WHERE ID = @i_Count)
 SELECT @SQL = '
 SELECT '''+REPLACE(@DLName,@DomNaim,'')+''',name FROM OPENQUERY( ADSI,''SELECT name, memberof
 FROM '''''+@LDAP+'''''
 WHERE memberOf='''''+@DLName+''''' '')'
 SET @command = CONVERT(NVARCHAR(4000),@SQL)
 IF @DebugMe = 0 BEGIN PRINT @command END
 IF @DebugMe = 1
 BEGIN
 INSERT INTO #Output
 EXEC sp_executesql @command END
 SET @i_Count = @i_Count + 1ENDSELECT (select s from dbo.SplitString(DL, ',') where zeroBasedOccurance=0) CN
, (select s from dbo.SplitString(DL, ',') where zeroBasedOccurance=1) OU1
, (select s from dbo.SplitString(DL, ',') where zeroBasedOccurance=2) OU2
, (select s from dbo.SplitString(DL, ',') where zeroBasedOccurance=3) OU3
, (select s from dbo.SplitString(DL, ',') where zeroBasedOccurance=4) OU4 /*See the pattern, add more if needed*/, name /*,DL*/
FROM #Output
IF OBJECT_ID(N'tempdb..#dnTable', N'U') IS NOT NULL BEGIN DROP TABLE #dnTable END
IF OBJECT_ID(N'tempdb..#Output', N'U') IS NOT NULL BEGIN DROP TABLE #Output END

..run it. The results should look something like this:

OU=MyDistributionLists, OU=DL, NULL, NULL,Mister Man

OU=MyDistributionLists, OU=DL, NULL, NULL, Iam Boss

OU=MyDistributionLists, OU=DL, NULL, NULL, Madam LadyCN

CN=MyGreatDL, CN=AnotherFantasticDL, CN=Oooh, DL

Now you can paste the results into your favourite data manipulation application, in my case Excel, and pivot it.

I only allowed for about 4 levels deep into the AD and only 1 domain, but it should cover about 99.99% of requirements for most people.

Adrian