Windows Group Membership Checker

,

Introduction :

A script to reveal which logins have access to your sql server via windows groups.

If you use Windows Authentication and have Windows groups as logins this script can help.
It reveals members of Windows groups and their Server permissions.

This is particularly handy if you're not the network administrator or dont have access to the Active Directory tools.

Hope it helps someone...

r

 

Richard Doering

http://sqlsolace.blogspot.com

/*
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]

Rate

3.69 (13)

Share

Share

Rate

3.69 (13)