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