Select Group Members from Logins

  • slesicki

    SSC Enthusiast

    Points: 196

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

  • Jeff Moden

    SSC Guru

    Points: 996654

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • tskelley


    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.

    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