Tsql: How to Get a List of Groups That a User Belongs to in Active Directory

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

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.

Check users in a security group in SQL Server

Checking yourself or the current user:

SELECT IS_MEMBER('[group or role]')

A result of 1 = yes,0 = no, and null = the group or role queried is not valid.

To get a list of the users, try xp_logininfo if extended procs are enabled and the group in question is a windows group :

EXEC master..xp_logininfo 
@acctname = '[group]',
@option = 'members'

Return a list of all Active Directory groups a user belongs to in string[ ]

This should do the trick.

using System.DirectoryServices.AccountManagement;

public static string[] GetGroups(string username)
{
    string[] output = null;

    using (var ctx = new PrincipalContext(ContextType.Domain))
    using (var user = UserPrincipal.FindByIdentity(ctx, username))
    {
        if (user != null)
        {
            output = user.GetGroups() //this returns a collection of principal objects
                .Select(x => x.SamAccountName) // select the name.  you may change this to choose the display name or whatever you want
                .ToArray(); // convert to string array
        }
    }

    return output;
}

How to retrieve SQL Server database roles for an Active Directory domain group

In the GetCurrentUserCountry() function I used the system function IS_MEMBER('MyDomain_Sales_USA') and IS_MEMBER('MyDomain_Sales_GER'), this works fine but has a small disadvantage cause I have to update the function everytime a new country group is created.

Active directory check if user belongs to a group

You can get the list of groups a user is a member of by querying the memberOf navigation property on the user object.

Read about it here.

https://graph.windows.net/myorganization/users/{user_id}/$links/memberOf?api-version

Note that you can remove the $links part of the query to return the whole group object, rather than the link to the object. However, for simply validating a user is a member of a certain group, you can use the links, and compare the object id of the groups that are returned to the one you are looking for.

How to retrieve each parent group of an Active Directory group

You can read the memberOf attribute of the group, using IADs::GetEx.

If you have an IADsGroup group_object:

HRESULT hr;
VARIANT groups;
VariantInit(&groups);
hr = group_object->GetEx(CComBSTR("memberOf"), &groups);

The groups variable will now be a VARIANT array containing the distinguishedName of all the groups. If you want to get the friendly name of each one, then you'll need to bind to each group (using ADsGetObject) to get an IADsGroup object for that group.

The memberOf attribute does have some caveats that you should be aware of, which I wrote about here, but if you're on a single-domain environment with no external, trusted domains, then it shouldn't matter to you.

Note that the return value hr might be E_ADS_PROPERTY_NOT_FOUND if it is not a member of any other groups. Active Directory in general treats empty attributes as non-existent.

---

Related Topics