locked
Query active directory groups and add users to Datagrid RRS feed

  • Question

  • User775589824 posted

    Never done this and don't know how to begin.  I need to query  9 groups in active directory for members and then take them members and add them to a datagrid where I can pull other information that deals with them.  How can I do this.  Please Help. Thanks

    Thursday, February 12, 2009 12:45 PM

All replies

  • User-796298121 posted

    Try using these sites to get you started.  It helped me out a lot when I was doing something similar.

    http://www.willasrari.com/blog/query-active-directory-users-using-c/000133.aspx
    http://computerperformance.co.uk/Logon/LDAP_attributes_active_directory.htm
    http://msdn.microsoft.com/en-us/library/aa746475(VS.85).aspx
    http://msdn.microsoft.com/en-us/library/system.directoryservices.aspx

    It doesn't completely answer your question but it should help you to get started.  Then you can post back with any questions that you have after trying to apply it.

    Basically you need to filter by the group and once you get back all this information, you can store it into a DataSet (or something like that) and then link that to your DataGrid.

    Thursday, February 12, 2009 3:36 PM
  • User775589824 posted

    ok I got this to work, but I need help on the next part, I need to make this a stored procedure.  How can I do this. 

    create table #MemberOfGroups(

    groepNaam varchar(400),

    cn varchar(400),

    displayName varchar(400),

    mail varchar(400))

    SET NOCOUNT ON

    declare @t varchar(100),@t2 varchar(1000), @t3 varchar(1000), @ot varchar (4000), @tt varchar (4000);

    declare gC cursor

    for

    select cn, distinguishedName, mail

    from openquery

    (ADSI,'SELECT cn, distinguishedName, mail

    FROM ''LDAP://

    WHERE objectCategory = ''group''

    and CN = ''NGMN SEC J6 Automation FTS''

    or CN = ''NGMN SEC J6 Help Desk (Level 1)''

    or CN = ''NGMN SEC J6 Help Desk (Level 2)''

    or CN = ''NGMN SEC J4 Admins''

    or CN = ''NGMN SEC J8 Admins''

    or CN = ''NGMN SEC FMO Admins''

    or CN = ''NGMN SEC RRM Admins''

    or CN = ''MN Admins''

    ')

    open gC

    FETCH NEXT FROM gC INTO @t, @t2, @t3

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set
    @ot = '''SELECT cn, displayName, mail

    FROM ''''LDAP://

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

    AND memberOf=''''' + @t2 + '''''';

    set @tt = 'select '+ ''''+@t+'''' +' As GroepNaam, cn, displayName, mail from openquery(ADSI,'+ @ot +''') order by cn'

    insert into #MemberOfGroups(groepNaam, cn, displayName, mail)

    EXEC (@tt)

    --print @tt

    FETCH NEXT FROM gC INTO @t, @t2, @t3

    END

    CLOSE gC

    DEALLOCATE gC

    Delete from [IAComplianceDotNet].[dbo].[tblADAccounts]

    INSERT INTO [IAComplianceDotNet].[dbo].[tblADAccounts]

    ([strName]

    ,[strAccount]

    ,[strEmail])

     

    select Distinct(CN), groepNaam as 'Group', mail from #MemberOfGroups Where lower(substring(CN, 1, 3)) <> 'svc' order by CN

    --order by displayName

    drop table #MemberOfGroups

    Thursday, February 12, 2009 3:58 PM
  • User-1001625923 posted

    You can use Enterprise Manger(SQL server 2000) or SQL server 2005 mangement studio to connect to the SQL server, then on the stored procedure to create new stored procedure, copy all your code, then past there, and then save. You may need to click the stored procedure's properties to grant correct permissions. hope this will help.

    Monday, June 8, 2009 6:33 PM