September 1, 2006 at 7:49 am
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
September 4, 2006 at 8:00 am
This was removed by the editor as SPAM
November 10, 2010 at 9:56 am
Any workaround for the question, now am facing this issue.....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply