Can we use 'xp_logininfo' to compare and find duplicate groups?

Question

With 'xp_logininfo' we can pull members of AD Security Groups

/* all groups user is member and group granting access */
EXEC   xp_logininfo 'Domain\WindowsUser', 'all';

/* all user is in group */
EXEC   xp_logininfo 'Domain\WindowsGroup', 'members';

Problem: I have inherited many security groups and found duplicate: Groups either have the same members but different access or Same access different members in groups Some groups no longer have members at all

Idea: I want to find all the Windows Groups drop them in a table then (and this is where I need a smarted DBA) create a new table using 'xp_logininfo' to execute on every name name in the first table

select
      [name]
from [sys].[database_principals]
where [type] in ('G')

Insert Into #temptable1 ([name])

(theory)
EXEC   xp_logininfo '#temptable1', 'members';
CREATE TABLE #temptable2 ( [account name] nvarchar(128), [type] varchar(8), [privilege] varchar(8), [mapped login name] nvarchar(128), [permission path] nvarchar(128) )

INSERT INTO #temptable2 ([account name], [type], [privilege], [mapped login name], [permission path])

So far I am able to pull members of AD Security Groups 1 at a time but with over 800 groups just collecting the data and moving it into excel to compare has been a time consuming task.

If anyone has done this or has an idea how I can speed this up I'd love the assistance.

I'd like to add I'm dyslexic so if I need to re-explain any of this please let me know.

Answer 1

Update: I found this: Enumerate Windows Group Members Lowell Izaguirre, 2017-02-24 (first published: 2016-03-17)

Info - https://www.sqlservercentral.com/articles/enumerate-windows-group-members Code - https://www.sqlservercentral.com/wp-content/uploads/2019/05/EnumerateWindowsGroupMembers.sql

I used this to write the results to a table and compared the data

    ...
    --###############################################################################################
    -- Quick script to enumerate Active directory users who get permissions from An Active Directory Group
    --###############################################################################################


    --a table variable capturing any errors in the try...catch below
    DECLARE @ErrorRecap TABLE
      (
         ID           INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
         AccountName  NVARCHAR(256),
         ErrorMessage NVARCHAR(256)
      ) 

    IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
    DROP TABLE #tmp
    --table for capturing valid resutls form xp_logininfo
    CREATE TABLE [dbo].[#TMP] ( 
    [ACCOUNT NAME]       NVARCHAR(256)                        NULL ,
    [TYPE]               VARCHAR(8)                           NULL ,
    [PRIVILEGE]          VARCHAR(8)                           NULL ,
    [MAPPED LOGIN NAME]  NVARCHAR(256)                        NULL ,
    [PERMISSION PATH]    NVARCHAR(256)                        NULL )

    DECLARE @groupname NVARCHAR(256)
      --better practice cursor: all options
     declare c1 cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
      --###############################################################################################
      --cursor definition
      --###############################################################################################
       SELECT name 
       FROM master.sys.server_principals 
       WHERE type_desc =  'WINDOWS_GROUP' 
       --###############################################################################################
      OPEN c1
      FETCH NEXT FROM c1 INTO @groupname
      WHILE @@FETCH_STATUS <> -1
        BEGIN
          BEGIN TRY
            INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])
              EXEC master..xp_logininfo @acctname = @groupname,@option = 'members'     -- show group members
          END TRY
          BEGIN CATCH
            --capture the error details
            DECLARE @ErrorSeverity INT, 
                    @ErrorNumber INT, 
                    @ErrorMessage NVARCHAR(4000), 
                    @ErrorState INT
            SET @ErrorSeverity = ERROR_SEVERITY()
            SET @ErrorNumber = ERROR_NUMBER()
            SET @ErrorMessage = ERROR_MESSAGE()
            SET @ErrorState = ERROR_STATE()

            --put all the errors in a table together
            INSERT INTO @ErrorRecap(AccountName,ErrorMessage)
              SELECT @groupname,@ErrorMessage

              --echo out the supressed error, the try catch allows us to continue processing, isntead of stopping on the first error
            PRINT 'Msg ' + convert(varchar,@ErrorNumber) + ' Level ' + convert(varchar,@ErrorSeverity) + ' State ' + Convert(varchar,@ErrorState)
            PRINT @ErrorMessage
        END CATCH
        FETCH NEXT FROM c1 INTO @groupname
        END
      CLOSE c1
      DEALLOCATE c1
    --display both results and errors
    SELECT * FROM #tmp
    SELECT * FROM @ErrorRecap

    ...