Active Directory - getting users and groups belonging to a specific group

Question

First of all, I needed to get a list of users who are members of a particular group in SQL Server. I managed to find something on a network that works fine. The solution is shown as follows:

CREATE PROCEDURE [dbo].[NES_GetADGroupMembers]
    (@groupName VARCHAR(max))
AS 
BEGIN
    CREATE TABLE #MemberOfGroups
    (
         groupName varchar(400),
         cn varchar(400),
         displayName varchar(400)
    )

    SET NOCOUNT ON

    DECLARE @t varchar(100), @t2 varchar(1000), 
            @ot varchar (4000), @tt varchar (4000);

    DECLARE gC CURSOR FOR
        SELECT cn, distinguishedName
        FROM openquery (ADSI, 'SELECT cn, distinguishedName
                               FROM ''''LDAP://Mydomaindomain/CN=users,DC=Mydomain,DC=com''''
                               WHERE objectCategory = ''group''')

    OPEN gC

    FETCH NEXT FROM gC INTO @t, @t2

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ot = '''SELECT cn, displayName
                   FROM ''''LDAP://Mydomaindomain/CN=users,DC=Mydomain,DC=com'''' 
                   WHERE objectCategory = ''''Person'''' AND objectClass = ''''user''''
                     AND memberOf=''''' + @t2 + '''''';

       SET @tt = 'select '+ ''''+@t+'''' +' As groupName, cn, displayName from openquery(ADSI,'+ @ot +''') order by cn'

       INSERT INTO #MemberOfGroups (groupName, cn, displayName)
           EXEC (@tt) 

       FETCH NEXT FROM gC INTO @t, @t2
    END

    CLOSE gC
    DEALLOCATE gC

    SELECT 
        groupName, displayName 
    FROM
        #MemberOfGroups

So far, it just works just fine, returning users to the entered group.

However, I have a new query in which a group can have other “groups” as a member, as well as users.

I tried updating my filter in the WHERE clause to include objectClass = ''group'', but it does not work.

Does anyone know about these queries and can also help me get groups (input group members)?

Answer 1

, OU () ? AD CN SecurityGroups. :

SELECT cn, member FROM ''''LDAP://Mydomaindomain/CN=<actual OU of groups?>,DC=Mydomain,DC=com''''

as member DN . ( !). , , DN, .