ADSI Get Users Groups

[mfaulcon]

I have a linked server setup so that I can query Active Directory and ADAM. I
have veiws for Users and Groups, and have even written a query to return the
members of a given group. The problem is, I need to write the reverse, a sp
to return the groups a given user is a member of.

Does anyone know how to do this? Environment is SQL Server 2005, Win 2K3 R2.

Thanks,
Mark Faulcon

[Russell Fields]

Mark,

Assuming that you have stored the active directory group names inside the
SQL Server (so that your code has access) then:

EXECUTE AS LOGIN='DOMAIN\LOGIN' -- The account you are trying to answer for

SELECT SUSER_SNAME (), name FROM DomainGroups
WHERE IS_MEMBER(name) = 1

REVERT

Using this approach, you will have to loop through all of the logins
individually, so open a cursor on Logins and loop through this for all
logins. Insert into a table, rathe than just selecting. This code does not
tell you how deeply nested the membership is, just that it exists.

RLF

"mfaulcon" <mfau...@discussions.microsoft.com> wrote in message
news:9CB14323-D97C-4037...@microsoft.com...

[Linchi Shea]

I haven't use ADSI directly through a linked server.

But I've done this vai a script:
1) get the AD path from a domain account in the format of Domain\User using
DSQuery.exe user -samid Domain\User

2) use the following script to get the groups:
Set objUser = GetObject(<dn>)
Set colGroups = objUser.Groups
For Each objGroup in colGroups
Wscript.Echo objGroup.CN
Next

Linchi