Select Group Members from Logins

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

    yyyy-mm-dd Init Description
    2018-02-14 PLS Created



    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)
    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;

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


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

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