Post reply

Query AD for Group Membership

  • November 24, 2010 at 6:09 pm

    #243213

    I just want to find out the details or list the emmbers of a Particular Distibutin List by connecting through SQL. I understand tat there is a limitation of 1000 record, but my DL is not that big.

    My requirement is not very dynamic : Just list the members of a particular DL

    I followed the following but it gives a list of users but not the member of the DL.

    Step 1: Create a linked server to your Active Directory

    sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

    Step 2: Create a view in SQL server using OPENQUERY to select from Active Directory

    CREATE VIEW dbo.vw_AD_USER_INFO

    AS

    SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whaever,DC=domain,DC=org'' where objectClass = ''User''')

    GO

  • November 29, 2010 at 5:52 pm

    #1256553

    Anyone who can help pelase...

  • April 11, 2011 at 9:58 am

    #1309931

    Try this code,

    I use it to list of the members of a specific AD group or distribution list.

    You can modify to display the fields you want.

    EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',

    @srvproduct = N'Active Directory Service Interfaces',

    @provider = N'ADSDSOObject', @datasrc = N'YOURDOMAINNAME'

    GO

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ADSI', @useself = N'True',

    @locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL

    GO

    CREATE PROCEDURE [dbo].[sp_GetADGroupMembers]

    (

    @groupName VARCHAR(35) ,

    @OrganizationalUnit VARCHAR(35)

    )

    AS

    DECLARE @tsql VARCHAR(4000)

    SET @tsql = 'SELECT sn LastName,GivenName FirstName,sAMAccountName DomainAccount,

    department,manager,employeeID

    FROM OPENQUERY(ADSI,'

    + '''SELECT sn,GivenName,sAMAccountName,department,manager,employeeID,userAccountControl

    FROM ''''LDAP://DC=YOURDOMAIN,DC=YOURDOMAIN''''

    WHERE objectCategory = ''''Person'''' AND objectClass = ''''user''''

    AND memberOf=''''CN=' + @groupName + ',OU=Groups,OU=' + @OrganizationalUnit

    + ',DC=YOURDOMAIN,DC=YOURDOMAIN'''' ''' + ')'

    EXEC(@tsql)

    QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply