Active Directory Groups With SQL Server 2005

Question

Alright, so I already have an existing connection to Active Directory on my server. I know that querying active directory works. What I want to be able to do is query for all of the ou's and/or groups in active directory, and also be able to find the users the belong to those groups/ous.

this is the current query that just pulls user information (or part of a stored procedure set up to pull all users):

SELECT
    userAccountControl,
    DisplayName AS [NAME],
    givenName AS FIRSTNAME,
    middleName, 
    sn AS LASTNAME,
    employeeID AS EMPID,
    telephoneNumber AS EXT,
    Title, 
    Department AS DEPT,
    Division,
    sAMAccountName AS UserName,
    mail AS Email,
    homeDirectory AS HomeDir,
    userPrincipalName AS LOGON,
    manager
FROM         OPENQUERY(ADSI, 
                      '
 select
     userAccountControl,
     DisplayName,
     givenName,
     middleName,
     sn,
     employeeID,
     telephoneNumber,
     Title,
     Department,
     Division,
     sAMAccountName,
     mail,
     homeDirectory,
     userPrincipalName,
     manager
 from ''LDAP://name''
 where sn > ''a''
    and sn <''h''
 order by DisplayName
 ')
AS derivedtbl_1

Tomalak Tomalak · Accepted Answer · 2009-05-05T15:19:04

Are you aware of the fact that you cannot query more objects than the AD server is willing to return in one reply?

The ADSI SQL provider does not support paging through the results. The AD server is usually configured to return the first 1000 results only.

If you query for virtually all AD objects at once you are very likely to hit that limit.

Can you clarify what you are trying to achieve?

Active Directory Groups With SQL Server 2005

1 Answers