Query LDAP user group membership from SQL Server

this is driving me nuts!
all i want is a simple list of groups and members for each group in
Active Directory. I read numorous postings on this topic and no one
seems to know how to query it from sql server directly.
why can't Microsoft provide some kind of schema (views) for that?
i can issue a query like this
SELECT a.name, a.adspath, b.name, b.adspath
FROM OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://server/ DC=mydomain,DC=com''
WHERE objectCategory = ''person''') a,
OpenQuery(ADSI,
'SELECT name, ADsPath
FROM ''LDAP://server/ DC=mydomain,DC=com''
WHERE objectCategory = ''group''') b
but there is no relationship i can join between the two to connect the
dots between groups and users.
the problems i have is that i'm not a VB programmer, and i am not a
network admin and don't knwo how to use some of the vb code samples
provided in the newsgroup (see below). unless someone has a more
comprehesive link for how to set those scripts up.
---
To the best of my knowledge, you can retrieve MemberOf in
your list of attributes, but you cannot query on it. You
must return a recordset with memberof among the
attributes, then enumerate the recordset and look for the
info you need. MemberOf will be an array. Use:
'<LDAP://myServer/cn=users,dc=myDomain,dc=com>;(&
(objectCategory=Person)(objectClass=user
))
;displayname, memberOf, objectCategory, cn,
adspath;subtree'
In your example, that means returning a recordset of all
users. If RS is the recordset, I code the following in
VBScript.
colMembers = RS.Fields("MemberOf")
For Each Item in colMembers
Wscript.Echo Item
---
either that, or does anyone knows how to script out that info from
Active Directory and output it to a text file for sql to pick up? I
just want a simple two column file to tell me all the groups and
members for each group. why would it be difficult?Hi
You need to look at the memberof attribute.
http://msdn.microsoft.com/library/d...ace_mapping.asp
This may also help
http://www.rlmueller.net/List%20User%20Groups.htm
John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1108757700.331401.193420@.o13g2000cwo.googlegroups.com...
> this is driving me nuts!
> all i want is a simple list of groups and members for each group in
> Active Directory. I read numorous postings on this topic and no one
> seems to know how to query it from sql server directly.
> why can't Microsoft provide some kind of schema (views) for that?
> i can issue a query like this
> SELECT a.name, a.adspath, b.name, b.adspath
> FROM OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://server/ DC=mydomain,DC=com''
> WHERE objectCategory = ''person''') a,
> OpenQuery(ADSI,
> 'SELECT name, ADsPath
> FROM ''LDAP://server/ DC=mydomain,DC=com''
> WHERE objectCategory = ''group''') b
> but there is no relationship i can join between the two to connect the
> dots between groups and users.
> the problems i have is that i'm not a VB programmer, and i am not a
> network admin and don't knwo how to use some of the vb code samples
> provided in the newsgroup (see below). unless someone has a more
> comprehesive link for how to set those scripts up.
> ---
> To the best of my knowledge, you can retrieve MemberOf in
> your list of attributes, but you cannot query on it. You
> must return a recordset with memberof among the
> attributes, then enumerate the recordset and look for the
> info you need. MemberOf will be an array. Use:
> '<LDAP://myServer/cn=users,dc=myDomain,dc=com>;(&
> (objectCategory=Person)(objectClass=user
))
> ;displayname, memberOf, objectCategory, cn,
> adspath;subtree'
> In your example, that means returning a recordset of all
> users. If RS is the recordset, I code the following in
> VBScript.
> colMembers = RS.Fields("MemberOf")
> For Each Item in colMembers
> Wscript.Echo Item
> ---
> either that, or does anyone knows how to script out that info from
> Active Directory and output it to a text file for sql to pick up? I
> just want a simple two column file to tell me all the groups and
> members for each group. why would it be difficult?
>|||thanks for the reply. but i'm not trying to look up a window user
account's group info. i can see that in Active directory. what i need
is a way to script out all the groups and users info from AD and there
should a membership relationship like
user memberof
u1 grp1
u1 grp2
u1 grp3
u2 grp2...
and so on.
again, i'm not a vb programmer, so i need some intructions as to how to
run a script and so on. i have seen those links you posted before. i
couldnt' get them to work. something is missing from the instructions.
can anyone fill the gap?
thank you.|||Yeah John but have u been able to retrieve GROUPS from the Active
Diretory?
For example i want to get a USER and retrieve the GROUPS they belong to.
Any LUCK using OPEN QUERY?
*** Sent via Developersdex http://www.examnotes.net ***