CREATE PROCEDURE BD_AccesViaWindowsGroupMembership_prc

as

DECLARE @CurrentRow INT
DECLARE @TotalRows INT
SET @CurrentRow = 1

DECLARE @SqlGroupMembershipTbl
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

WHILE @CurrentRow <= @TotalRows
BEGIN

SELECT @WindowsGroupName = [Name]
FROM @WindowsGroupsOnServer
WHERE UniqueRowID = @CurrentRow

BEGIN TRY
INSERT INTO @SqlGroupMembershipTbl (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH)
EXEC xp_logininfo @WindowsGroupName , ‘members’
END TRY

BEGIN CATCH
— No action
END CATCH

SELECT @CurrentRow = @CurrentRow + 1

END

SELECT @@servername AS Servername
, [PERMISSION_PATH] AS WindowsGroup
, ACCOUNT_NAME
, MAPPED_LOGIN_NAME
, ACCOUNT_TYPE
, ACCOUNT_PRIVILEGE
FROM @SqlGroupMembershipTbl
ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]