Cool Tips‎ > ‎SQL Server Security‎ > ‎

Enumerate Logins mapped via Groups

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Tested

Credits:

Author:Richard Doering
Date:    1 Aug 2010

Description

Get a list of domain logins that have access to the current server via domain group membership.
Includes all data returned by xp_logininfo with the 'members' parameter

Code


/* Script : SQL Server - Windows Group Membership Checker Version : 1.0 (August 2010) Author : Richard Doering Email : r3m1xn9@gmail.com Web : http://sqlsolace.blogspot.com */ DECLARE @CurrentRow INT DECLARE @TotalRows INT SET @CurrentRow = 1 DECLARE @SqlGroupMembership TABLE( ACCOUNT_NAME SYSNAME, ACCOUNT_TYPE VARCHAR(30), ACCOUNT_PRIVILEGE VARCHAR(30), MAPPED_LOGIN_NAME SYSNAME, PERMISSION_PATH SYSNAME ) DECLARE @WindowsGroupsOnServer TABLE( UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL , Name SYSNAME ) INSERT INTO @WindowsGroupsOnServer (NAME) SELECT [NAME] FROM master.sys.server_principals WHERE TYPE = 'G' SELECT @TotalRows = MAX(UniqueRowID) FROM @WindowsGroupsOnServer DECLARE @WindowsGroupName sysname -- Loop Each Windows Group present on the server WHILE @CurrentRow <= @TotalRows BEGIN SELECT @WindowsGroupName = [Name] FROM @WindowsGroupsOnServer WHERE UniqueRowID = @CurrentRow BEGIN TRY -- Insert found logins into table variable INSERT INTO @SqlGroupMembership (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH) EXEC xp_logininfo @WindowsGroupName , 'members' END TRY BEGIN CATCH -- No action for if xp_logininfo fails END CATCH SELECT @CurrentRow = @CurrentRow + 1 END -- Display final results SELECT @@servername AS Servername , [PERMISSION_PATH] AS WindowsGroup , Account_Name , Mapped_Login_Name , Account_Type , Account_Privilege FROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]





Comments