Select Group Members from Logins

/*-------------------------------------------------------------------------------------------------Name: LoginWindowsGroupMemberSelect.sqlPurpose: To find all user accounts for a Windows Group in SQL Server logins.Author: Patrick SlesickiNotes:Returns all user logins for a Windows GroupSimply execute on an instance to get results.Tested on SQL Server versions 2012 through 2017. This should work on 2008 and 2008R2 but I've not tested it there.Adapted from a presentation by Laura Grob.Historyyyyy-mm-dd Init Description2018-02-14 PLS Created---------------------------------------------------------------------------------------------------Preliminaries-------------------------------------------------------------------------------------------------*/SET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;/*-------------------------------------------------------------------------------------------------Declarations-------------------------------------------------------------------------------------------------*/DECLARE @WindowsGroupName AS nvarchar(128);DECLARE @GroupLoginTable AS table (AccountName nvarchar(128) NULL);DECLARE @GroupLoginMemberTable AS table(AccountName nvarchar(128) NULL,Type char(8) NULL,Privilege char(9) NULL,MappedLoginName nvarchar(128) NULL,PermissionPath nvarchar(128) NULL);/*-------------------------------------------------------------------------------------------------Find windows groups-------------------------------------------------------------------------------------------------*/INSERT INTO @GroupLoginTable (AccountName)SELECT nameFROM sys.server_principalsWHERE type_desc = N'WINDOWS_GROUP';/*-------------------------------------------------------------------------------------------------Cycle through groups to find members-------------------------------------------------------------------------------------------------*/WHILE EXISTS (SELECT * FROM @GroupLoginTable)BEGINSET @WindowsGroupName = (SELECT TOP (1) AccountName FROM @GroupLoginTable);INSERT INTO @GroupLoginMemberTable(AccountName,Type,Privilege,MappedLoginName,PermissionPath)EXEC sys.xp_logininfo@acctname = @WindowsGroupName,@option = 'members';DELETE FROM @GroupLoginTableWHERE AccountName = @WindowsGroupName;END;/*-------------------------------------------------------------------------------------------------Output results-------------------------------------------------------------------------------------------------*/SELECTWindowsGroup = PermissionPath,AccountName,PrivilegeFROM @GroupLoginMemberTableORDER BYWindowsGroup,AccountName;/*-------------------------------------------------------------------------------------------------END-------------------------------------------------------------------------------------------------*/