Query Active Directory and Return Member or MemberOf attributes

Posted on 2007-10-10
Last Modified: 2011-10-03
I am trying to query active directory to get the groups and their members.  I am trying to conduct access reviews.  When I try to get the 'member' attribute, I get the following error

Could not get the data of the row from the OLE DB provider 'ADSDSOObject'. Could not convert the data value due to reasons other than sign mismatch or overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned 0x80040e21:  Data status returned from the provider: [COLUMN_NAME=member STATUS=DBSTATUS_E_CANTCONVERTVALUE]]

From what I understand, the member attribute is returned as an array.  Is there a way to get that into SQL Server 2000?  I know I can get this via .net, but I would like to keep it all in SQL Server.
Question by:smcdrc
    LVL 51

    Accepted Solution

    CREATE TABLE ##Groups ( CN VARCHAR(128), DN VARCHAR(1024), Email VARCHAR(128), ADSPath VARCHAR(1024) );
    CREATE TABLE ##Users ( DistributionGroup VARCHAR(128), FirstName VARCHAR(50), LastName VARCHAR(50), EmailAddress VARCHAR(128) );

    DECLARE @sql VARCHAR(1024)

    SET @sql = 'INSERT INTO ##Groups (CN, DN, Email, ADSPath) SELECT CN, distinguishedName DN, mail Email, ADSPath FROM OpenQuery(ADSI, ''<LDAP://,DC=com>;(&(objectClass=Group));cn, distinguishedName, mail, ADSPath;subtree'') ORDER BY distinguishedName';


    SELECT TOP 1 @CN = CN, @DN = DN FROM ##Groups


          SET @sql = 'INSERT INTO ##Users (DistributionGroup, Firstname, LastName, EmailAddress) SELECT '''+@CN+''' [Distribution Group], ISNULL(givenName, '''') FirstName, ISNULL(sn, '''') LastName, mail EmailAddress FROM OpenQuery(ADSI, ''<LDAP://,DC=com>;(&(objectClass=User)(memberOf='+@DN+'));givenName, sn, mail;subtree'')';

          SELECT TOP 1 @CN = CN, @DN = DN FROM ##Groups WHERE DN > @DN

    SELECT * FROM ##Groups;
    SELECT * FROM ##Users;

    DROP TABLE ##Groups;
    DROP TABLE ##Users;
    LVL 2

    Author Comment

    Thanks, Worked great the first time.
    LVL 51

    Expert Comment

    You are welcome.  This is a copy of what I did for someone else.  The tricky bit was learning how to do LDAP queries.

    Expert Comment

    I'm running the code exactly as shown above and although it appears to work fine it fails to return all the users in a group, stopping at 12 or 13 out of 22.  Is there someting you are aware of that would prevent all users from being returned?  Running a utility such as ldap.exe I am able to see all members of a group.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now