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]