We receiving have been working with auditors to provide a list of SQL logins that exist in each database role defined. Here is a short script to return all the members of each group that can easily be copied into Excel and shared with auditors and anybody else looking for this information. It basically cursors through each role defined in the database and stores the result of sp_helprolemember into a temp table then displays the results.
DECLARE rolemembers CURSOR READ_ONLY
FOR
SELECT name
FROM sysusers
WHERE issqlrole = 1
DECLARE @name SYSNAME
OPEN rolemembers
CREATE TABLE #userroles
(
DBRole SYSNAME ,
MemberName SYSNAME ,
MemberSID VARBINARY(64)
)
FETCH NEXT FROM rolemembers INTO @name
WHILE ( @@fetch_status <> -1 )
BEGIN
IF ( @@fetch_status <> -2 )
BEGIN
INSERT INTO #userroles
EXEC sp_helprolemember @name
END
FETCH NEXT FROM rolemembers INTO @name
END
CLOSE rolemembers
DEALLOCATE rolemembers
GO
SELECT DBRole ,
MemberName
FROM #userroles
DROP TABLE #userroles
