SQL Server and Active Directory: Query group members

• Dr.D

  Old Hand

  Points: 391

  More actions

  September 1, 2006 at 7:49 am

  #116497

  Hello,

  I have an urgent task: Retrieve all group members from active directory using SQL Server 2000 (query).

  I have created a linked server called ADSI and I can query all users and all groups by:

  /////////////// ALL USERS ////////////

  SELECT sAMAccountname AS AccountName, GivenName AS [First Name], SN AS [Last Name],

  mail AS [E-mail Address],

  TelephoneNumber AS [Telephone Number], l AS Office,

  PostalAddress AS [Postal Address], PostalCode, StreetAddress AS [Street Address],

  wWWHomePage AS [Web Page], distinguishedname AS DN

  FROM OPENQUERY(ADSI,

  'SELECT GivenName, SN, company, department, TelephoneNumber, mail, distinguishedname, title, l, manager, mobile, name, PhysicalDeliveryOfficeName, PostalAddress, PostalCode, sAMAccountname, StreetAddress, wWWHomePage

  FROM ''LDAP://MyDomain/DC=Domain,DC=com,DC=gr''

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

  ORDER BY Name')

  Rowset_1

  ///////////////

  ////////////// ALL GROUPS ///////////

  SELECT sAMAccountname AS AccountName, GivenName AS [First Name], SN AS [Last Name],

  mail AS [E-mail Address],

  TelephoneNumber AS [Telephone Number], l AS Office,

  PostalAddress AS [Postal Address], PostalCode, StreetAddress AS [Street Address],

  wWWHomePage AS [Web Page], distinguishedname AS DN

  FROM OPENQUERY(ADSI,

  'SELECT GivenName, SN, company, department, TelephoneNumber, mail, distinguishedname, title, l, manager, mobile, name, PhysicalDeliveryOfficeName, PostalAddress, PostalCode, sAMAccountname, StreetAddress, wWWHomePage

  FROM ''LDAP://MyDomain/DC=Domain,DC=com,DC=gr''

  WHERE objectCategory = ''group'' AND objectClass = ''group''

  ORDER BY Name')

  Rowset_1

  ////////////////////////////////////////////

  but I need to get something like this:

  USER    FullName        Group

  --------------------------------------------

  User1     User1 User     Administrators

  User1     User1 User     IT

  User1     User1 User     Reader

  User2     User2 User     Reader

  User3     User3 User     Writer

  User3     User3 User     Log Reader

  User3     User3 User     Ext Connect

  It is urgent...Can somebody help me?

  Thank you in advance

• latheesh.jaya

  SSChasing Mays

  Points: 644

  More actions

  November 10, 2010 at 9:56 am

  #1249020

  Any workaround for the question, now am facing this issue.....