Prev: Table Spool/Lazy Spool is a symptom of what?
Next: Convert varchar to datetime
From: === Steve L === on 18 Feb 2005 15:15
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?

From: John Bell on 20 Feb 2005 06:15
Hi

You need to look at the memberof attribute.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ad/ad/user_object_user_interface_mapping.asp

This may also help

http://www.rlmueller.net/List%20User%20Groups.htm

John

"=== Steve L ===" <steve.lin(a)powells.com> wrote in message
news:1108757700.331401.193420(a)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?
>


From: === Steve L === on 21 Feb 2005 12:23
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.

 | 
Pages: 1
Prev: Table Spool/Lazy Spool is a symptom of what?
Next: Convert varchar to datetime