CODEWITHSUNDEEP
sql-server-2005active-directoryldap
DForck42
DForck42

Reputation: 20367

Active Directory Groups With SQL Server 2005

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

Upvotes: 0

Views: 1188

Answers (1)

Tomalak
Tomalak

Reputation: 338316

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?

Upvotes: 1

Related Questions