TSQL: How to get a list of groups that a user belongs to in Active Diretory

Question

I have two queries that retrieve all groups and all users in a domain, Mydomain

--; Get all groups in domain MyDomain
select *  
from OpenQuery(ADSI, '
 SELECT samaccountname,mail,sn,name, cn, objectCategory
 FROM ''LDAP://Mydomain/CN=users,DC=Mydomain,DC=com'' 
 WHERE objectCategory=''group'' 
 ORDER BY cn
 ')

--; Get all users in domain MyDomain
select *  
from OpenQuery(ADSI,'
 SELECT objectCategory, cn, sn, mail, name, department,samaccountname
 FROM ''LDAP://Mydomaindomain/CN=users,DC=Mydomain,DC=com'' 
 WHERE objectCategory=''user'' 
 ORDER BY cn
 ')
-- where samaccountname='mylogin'

What I would like to find out is,

How do you retrieve a list of all groups in MyDomain that a particular user belongs to?

[UPDATE] I was able to get the opposite result
Given the group name, retrieve all users

select *  
from OpenQuery(ADSI,
 'SELECT objectCategory, cn, sn, mail, name, department
 FROM ''LDAP://Mydomain/CN=users,DC=wl-domain,DC=com'' 
 WHERE MemberOf=''cn=_____GROUPNAME_____,CN=users,DC=Mydomain,DC=com''
 ORDER BY cn' 
 )

Answer 1

The Microsoft Technet Script Center is a great resource for scripts

http://technet.microsoft.com/en-us/scriptcenter/default.aspx

Here is a script that claims to give out exactly what you want:

http://gallery.technet.microsoft.com/ScriptCenter/en-us/ab5400e2-489a-4738-9b85-508bcb5b75f8

Answer 2

I think this is one of the limitations of the T-SQL based AD interface - you cannot retrieve multi-valued attributes, e.g. attributes (like memberOf for the user) that have more than one value in them.

You can retrieve single-valued attributes like "sn" (surname = last name) or "givenName" and "mail" and so forth, but the SQL-based interface isn't capable of handling attributes like "memberOf" with several values assigned to them.

So I'm afraid you'll have to go another way for this problem - e.g. find and populate the group membership in managed code (separately outside of SQL Server, or possibly as a CLR assembly inside SQL Server).

UPDATE: see here (MSDN Support) for an explanation of limitation of the OPENQUERY AD provider:

Limitations
The process of using the OPENQUERY statement to pull information from an LDAP server does suffer from some limitations. The limitations can be circumvented in some cases, but in others the application design must be altered. An external application or COM object that uses ADSI to retrieve the information from the LDAP server and then build a table in SQL by using ADO or other data access methods is another viable method.

The first limitation is that multivalued properties cannot be returned in the result set to SQL Server. ADSI will read schema information from the LDAP server that defines the structure and syntax of the classes and attributes used by the server. If the attribute that is requested from the LDAP server is defined in the schema as being multi-valued it cannot be returned in an OPENQUERY statement.

Answer 3

You can achieve this by fetching all groups that contain the user in their member attribute, or better the user's LDAP path (distinguishedName). Here's a simple procedure doing that job.

CREATE PROCEDURE dbo.GetLdapUserGroups
(
    @LdapUsername NVARCHAR(256)
)
AS
BEGIN
    DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)

    SET @Query = '
        SELECT @Path = distinguishedName
        FROM OPENQUERY(ADSI, ''
            SELECT distinguishedName 
            FROM ''''LDAP://DC=domain,DC=com''''
            WHERE 
                objectClass = ''''user'''' AND
                sAMAccountName = ''''' + @LdapUsername + '''''
        '')
    '
    EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT 

    SET @Query = '
        SELECT name AS LdapGroup 
        FROM OPENQUERY(ADSI,''
            SELECT name 
            FROM ''''LDAP://DC=domain,DC=com''''
            WHERE 
                objectClass=''''group'''' AND
                member=''''' + @Path + '''''
        '')
        ORDER BY name
    '
    EXEC SP_EXECUTESQL @Query

END

-- Hilbert