How to get around 1000 records limit in Active Directory with T-SQL query?

Question

Code below works perfectly* if I limit the cursor to a few groups but if I let it run against our whole domain I encounter 1000 records limit in AD. Any ideas on how to get this paging?

*perfectly because it should only return a limited number of records which number below the 1000 record limit.

CREATE TABLE #MemberOfGroups
(
    groupName   VARCHAR (400),
    cn          VARCHAR (400),
    displayName VARCHAR (400)
);

SET NOCOUNT ON;

DECLARE @t AS VARCHAR (100), 
@t2 AS VARCHAR (1000), 
@ot AS VARCHAR (4000), 
@tt AS VARCHAR (4000);

DECLARE gC CURSOR
    FOR SELECT cn,
               distinguishedName
        FROM   OPENQUERY (ADSI, 'SELECT cn, distinguishedName
     FROM ''LDAP://DC=coal,DC=local''
     WHERE objectCategory = ''group''');

OPEN gC;

FETCH NEXT FROM gC INTO @t, @t2;

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ot = '''SELECT cn, displayName
     FROM ''''LDAP://DC=coal,DC=local''''
     WHERE objectCategory = ''''Person'''' AND objectClass = ''''user''''
     AND memberOf=''''' + @t2 + '''''';
        SET @tt = 'select ' + '''' + @t + '''' + ' As GroupName, cn, displayName from openquery(ADSI,' + @ot + ''') order by cn';
        INSERT INTO #MemberOfGroups (groupName, cn, displayName)
        EXECUTE (@tt);
        --print @tt
        FETCH NEXT FROM gC INTO @t, @t2;
    END

CLOSE gC;

DEALLOCATE gC;  

Bobsled

Answer 1

The SQL Server ADO querying into Active Directory is very limited and doesn't offer all the capabilities of a full-blown AD interface.

According to Active Directory MVP Richard Mueller on his web site, this limitation of 1000-1500 objects returned from a search cannot be circumvented or fixed in SQL Server AD searches:

There are two limitations you should be aware of. First, the OPENQUERY statement does not support multi-valued attributes. You cannot retrieve the values of multi-valued attributes, like memberOf. Second, the total number of records that can be retrieved is limited to 1500 (1000 in Windows 2000 Active Directory). Paging is not supported from an SQL distributed query, so this limitation cannot be overcome, except by modifying the Active Directory server limit for maxPageSize.

So I'm afraid you won't be able to do this directly from SQL Server - you'll have to find another way to achieve this.

Facesaving

There are options if you don't care about speed and are willing to be a little hacky. e.g you could fire off 26 queries for each group grabbing all users beginning with 'a' then 'b' etc and aggregating these into a temp table or similar. Other methods of partitioning may be more appropriate but this was the easiest to explain. – Odisodium