Retrieving the groups of an Active Directory user using T-SQL

[b***@gmail.com]

Hello!

I'm trying to retrieve the groups of an Active Directory user
using T-SQL. For this, I create a linked server and query the
Active Directory using OpenQuery. When I query for the
memberOf attribute of the user I can't get anything because
it is a multi-valued attribute.

So my question is that: How can I retrieve the groups (nested
ones and the primary one included) of an Active Directory
user using T-SQL? Please notice that I don't ask how to do
this in Visual Basic, C++ or C#. I need to do this in T-SQL
in the current design.

Best Regards,
-Baris Boyvat

[Joe Kaplan (MVP - ADSI)]

I can't think of an elegant way to do that in T-SQL with openquery. The
lack of mv attribute support is kind of a killer.

If you had the user's DN, you could search for all groups that included
their DN in the member attribute, but you have a potentially huge amount of
work to do to unravel the nesting.

Primary group would also be hard. You can get the user's primaryGroupID,
but turning the RID into a reference to the a group could be hard as you
would need to build a SID out of it based on the domain's SID in order to
resolve it back into a name.

Do you have other options?

Joe K.

Post by b***@gmail.com
Hello!
I'm trying to retrieve the groups of an Active Directory user
using T-SQL. For this, I create a linked server and query the
Active Directory using OpenQuery. When I query for the
memberOf attribute of the user I can't get anything because
it is a multi-valued attribute.
So my question is that: How can I retrieve the groups (nested
ones and the primary one included) of an Active Directory
user using T-SQL? Please notice that I don't ask how to do
this in Visual Basic, C++ or C#. I need to do this in T-SQL
in the current design.
Best Regards,
-Baris Boyvat

[b***@gmail.com]

Thanks Joe for your comments.

Post by Joe Kaplan (MVP - ADSI)
Do you have other options?

Actually I have also an option of using C++ code to retrieve the groups
and
then use that code in T-SQL for instance by means of a extended stored
procedure or a COM object. I could retrieve the groups using the Groups

function of IADsUser but sadly it returns only the security groups. If
I use
the WinNT provider than I can even get the primary group there though.
So
by comparing the groups returned from WinNT and LDAP providers I can
even find which group is the primary one.

So then what would be best way to retrieve all the groups (basically
security and distribution groups in any scope (i.e. domain local,
global,
universal) including nested groups and the primary groups) of a user
using
unmanaged C++ code?

Best Regards,
-Baris Boyvat

[Joe Kaplan (MVP - ADSI)]

Using the tokenGroups attribute is the tried and true way to get all of the
user's security groups, including nested groups and the primary group, in a
single call, although I believe it only returns DLGs from the current
domain. However, if you want the distribution groups as well, then you may
need to simply recurse backwards through the memberOf attribute.

If you are using AD 2003, there is a neat feature called attribute-scoped
query that allows you to execute a search with the scope of the query being
the values inside of a distinguished name attribute instead of a part of the
directory like a subtree or single container. Thus, you could search in a
user's memberOf attribute and find each group's memberOf attribute and its
group type.

Primary group is retrieved by hand by looking at the primaryGroupID of a
user and matching that value with the primaryGroupToken of a group, with the
primaryGroupToken value being the RID on the SID of the group in question.

Unfortunately, group member expansion is simply non-trivial in Windows,
especially when you need to include distribution groups.

Joe K.

Post by b***@gmail.com
Thanks Joe for your comments.

Post by Joe Kaplan (MVP - ADSI)
Do you have other options?

Actually I have also an option of using C++ code to retrieve the groups
and
then use that code in T-SQL for instance by means of a extended stored
procedure or a COM object. I could retrieve the groups using the Groups
function of IADsUser but sadly it returns only the security groups. If
I use
the WinNT provider than I can even get the primary group there though.
So
by comparing the groups returned from WinNT and LDAP providers I can
even find which group is the primary one.
So then what would be best way to retrieve all the groups (basically
security and distribution groups in any scope (i.e. domain local,
global,
universal) including nested groups and the primary groups) of a user
using
unmanaged C++ code?
Best Regards,
-Baris Boyvat