Select Group Members from Logins

Question

Post reply

Select Group Members from Logins

slesicki

SSC Enthusiast

Points: 191
More actions
March 13, 2020 at 4:07 pm

#3734123

Comments posted to this topic are about the item Select Group Members from Logins

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 1

Since the code came out all on one line, please consider resubmitted where the code looks more normal. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tskelley SSCommitted Points: 1870 More actions · Answer 2

A fair amount of cleanup...

/*-------------------------------------------------------------------------------------------------
Name: LoginWindowsGroupMemberSelect.sql
Purpose:To find all user accounts for a Windows Group in SQL Server logins.
Author:Patrick Slesicki
Notes:Returns all user logins for a Windows Group

Simply 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.

History
yyyy-mm-dd Init Description
2018-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 name
FROM sys.server_principals
WHERE type_desc = N'WINDOWS_GROUP';

/*-------------------------------------------------------------------------------------------------Cycle through groups to find members-------------------------------------------------------------------------------------------------*/
WHILE EXISTS (SELECT * FROM @GroupLoginTable)
BEGIN
SET @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 @GroupLoginTable
WHERE AccountName = @WindowsGroupName;
END;

/*-------------------------------------------------------------------------------------------------Output results-------------------------------------------------------------------------------------------------*/
SELECT WindowsGroup = PermissionPath,AccountName,Privilege
FROM @GroupLoginMemberTable
ORDER BY WindowsGroup, AccountName;

/*-------------------------------------------------------------------------------------------------END-------------------------------------------------------------------------------------------------*/