List Members in Each Database Role

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

Related Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.