Select Group Members from Logins

  • slesicki

    SSC Enthusiast

    Points: 194

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

  • Jeff Moden

    SSC Guru

    Points: 996046

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    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-------------------------------------------------------------------------------------------------*/



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

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