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 INTDECLARE @TotalRows INTSET @CurrentRow = 1DECLARE @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 @WindowsGroupsOnServerDECLARE @WindowsGroupName sysname-- Loop Each Windows Group present on the server WHILE @CurrentRow <= @TotalRowsBEGINSELECT @WindowsGroupName = [Name]FROM @WindowsGroupsOnServerWHERE UniqueRowID = @CurrentRowBEGIN 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 TRYBEGIN CATCH-- No action for if xp_logininfo fails END CATCHSELECT @CurrentRow = @CurrentRow + 1END-- Display final results SELECT @@servername AS Servername, [PERMISSION_PATH] AS WindowsGroup, Account_Name, Mapped_Login_Name, Account_Type, Account_PrivilegeFROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]