Windows Group Membership Checker

SQLServerCentral.com have published a script I use to look at Windows Group Membership. This is good if you use AD groups to manage security...

SSC : Windows Group Membership Checker



March 2011 Update : Putting the function here too now as SSC exclusivity period over...

/*
Script  : SQL Server - Windows Group Membership Checker
Version : 1.0 (August 2010)
Author  : Richard Doering
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]