Active Directory - Get users and groups, member of a particular group

Question

Primarily, I needed to retrieve a list of users who are members of a particular group, in SQL Server. I did managed to find something on the net, which is working perfectly. Solution is as shown:

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

For now, its just working fine, returning the users of the entered group.

However, I have a new request, whereby a group can have other 'groups' as member, just like the users.

I tried updating my filter in the WHERE condition to include objectClass = ''group'', but it's not working.

Anyone knows a bit about these queries and can help me retrieve the groups (members of the input group) also?

Roos

I think your issue is with the filter - take out the ObjectCategory=Person, as the "group" probably isn't a person, and see if you get an answer. – Alamo

I tried removing it and also adding an OR statement with groups, but in vain. – Roos

Answer 1

I assume that your groups are inside of the OU (folder) of users? My AD setup has groups in a separate CN called SecurityGroups. Technically you could do:

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

as member will give you the DN of all of the members of that group. Then you have the array of members and just have to match those to their corresponding user (or group!) objects. Because this returns essentially an associative array of DNs, you can filter for the group objects and nest a query to run that again for the members of those.

Melina

Using member is rendering to the following error; Cannot get the data of the row from the OLE DB provider "ADsDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow. – Roos

Answer 2

I will answer this with a query, provided a windows group has a login in a server, then I can see who are the members of such a group

It is not a complete solution, as you would then need to check if any of the member is itself a group and get the members of that group too, navigating recursively...

hope this helps in the first step.

                    -- inside sql server if you want to know the memmbers of a group

                    -- this is a group that has a login in the current server
                    use master
                    select * from sys.server_principals
                    where type_desc = 'WINDOWS_GROUP'



                    --here for example, I get one of the logins from above, a windows_group and 
                    -- I show all of its members, as they all share the same permissions
                    -- here is how I find them
                    DECLARE @NTLogin NVARCHAR(128); 
                    SET @NTLogin = 'my_company\DBA_Administrators'

                                DECLARE @UserList TABLE (
                                         [Account Name]       NVARCHAR(128) COLLATE LATIN1_GENERAL_CI_AS,
                                         [Type]               NVARCHAR(128) COLLATE LATIN1_GENERAL_CI_AS,
                                         [Privilege]          NVARCHAR(128) COLLATE LATIN1_GENERAL_CI_AS,
                                         [Mapped Login Name]  NVARCHAR(128) COLLATE LATIN1_GENERAL_CI_AS,
                                         [Permission Path]    NVARCHAR(128) )


                                INSERT INTO @UserList ([Account Name],[Type],[Privilege],[Mapped Login Name],[Permission Path]) 
                                EXEC master.dbo.xp_logininfo @NTLogin, 'all' --insert group information

                                INSERT INTO @UserList ([Account Name],[Type],[Privilege],[Mapped Login Name],[Permission Path]) 
                                EXEC master.dbo.xp_logininfo @NTLogin, 'members'

                    --here you can see then, who are the DBA_Administrators
                    select * from @UserList

Ria