Hi all,

I have been working on a way to return Active directory group membership from a linked AD server in SQL.

Its quite hard as you can`t return it from the group – you have to return it from the user. What i wanted was to return every group within an OU and its members.

I first found this person and thier code:

http://blog.sdbonline.com/blog/?tag=/sql+server

So i used this code and modified it somewhat. It now takes many more parameters and returns all group membership from an OU. You obviously have to set up the linked SQL server first.

One of the hardest parts of this was the fact that it seemed to have some kind of limit to how much you can query the AD. To get round this i put a wait in after a certain amount of groups (configured by the parameters). I found that every 500 waiting 2 minutes was the safest root. It also waits 2 minutes to start, in case you have run it recently.

ALTER PROCEDURE [dbo].[sp_getADGroupUsers]
@LDAPRoot nvarchar(200),
@OUGROUP nvarchar(200),
@OUUSERS nvarchar(200),
@Waittime nvarchar(10),
@WaitGap integer,
@ServerName nvarchar(30)

AS

BEGIN
DECLARE @SQLString nvarchar(4000)
DECLARE @group_name nvarchar(500)
DECLARE @group_cursor CURSOR
Declare @GroupSamAccountname nvarchar(100)
declare @i integer

--Create a temp table to hold the results
CREATE TABLE #ADSI (
GroupCN nvarchar(500),
SamAccountname nvarchar(100))

--build an SQL string to return the ADsPath for the requested group
set @SQLString = N'SET @group_cursor = CURSOR STATIC FOR SELECT SUBSTRING(ADsPath,'+ cast(len(@ldaproot)+1 as varchar) + ',LEN(ADsPath))

FROM OPENQUERY('+ @ServerName +',''SELECT ADsPath FROM ''''' + @LDAPRoot + @ouGROUP + '''''
WHERE objectClass = ''''Group'''' '') FOR READ ONLY; OPEN @group_cursor'

--Execute the statement (will return the ADsPath in @group_cursor
EXECUTE sp_executesql @SQLString, N'@group_cursor CURSOR OUTPUT',
@group_cursor OUTPUT

set @i = 0

FETCH NEXT FROM @group_cursor INTO @group_name
WHILE @@FETCH_STATUS = 0
BEGIN

--work out account name
set @GroupSamAccountname = substring(@group_name, 4, (len(@group_name) - len( @OUGROUP))-4)

--Get data for group
SET @SQLString = N'INSERT INTO #ADSI SELECT ''' + @GroupSamAccountname + ''',SamAccountname
FROM OPENQUERY('+ @ServerName +',''SELECT ADsPath,
SamAccountname FROM ''''' + @LDAPRoot + @OUUsers + '''''
WHERE objectCategory = ''''Person'''' and objectClass = ''''user''''
and memberOf=''''' + @group_name + ''''' '') ';

--execute the statement
EXECUTE sp_executesql @SQLString;

set @i = @i + 1

IF @i = @waitgap
BEGIN
WAITFOR DELAY @waittime
set @i = 0
END

FETCH NEXT FROM @group_cursor INTO @group_name
END

--return the resultset
--(use DISTINCT as a user might be a member of several groups)
SELECT DISTINCT * FROM #ADSI

--explicitely drop the temp table
DROP TABLE #ADSI
END 

And here is how you call it:

DECLARE @RC int
DECLARE @LDAPRoot nvarchar(200)
DECLARE @FriendlyGroupName nvarchar(200)
DECLARE @OUGROUP nvarchar(200)
DECLARE @OUUSERS nvarchar(200)
declare @waittime nvarchar(10)
declare @waitgap integer
declare @ServerName nvarchar(30)

set @ldaproot = 'LDAP://DC.domain.com/'

--Where are the groups you want to return
set @OUGROUP = 'OU=groups,DC=Domain,DC=co,DC=uk'
--Where are the users you want to return
set @OUUSERS = 'OU=users,DC=domain,DC=co,DC=uk'
--Waiting time between groups
set @waittime = '00:02:00'
--How big are the groups
set @waitgap = 500
--Your AD linked server in SQL
set @ServerName = 'ADSI'

EXECUTE @RC = [SystemsIntegration].[dbo].[sp_getADGroupUsers]
@LDAPRoot

,@OUGROUP
,@OUUSERS
,@waittime
,@waitgap
,@ServerName

Hope this helps someone,

Dan