sys.server_role_members contains the membership of each server principal with server roles. It returns a pair of role_principal_id and member_principal_id for each membership as below:

-- © 2011 – Vishal (http://SqlAndMe.com)
 
SELECT role_principal_id, member_principal_id
FROM   sys.server_role_members

Result Set:

role_principal_id member_principal_id
—————–            ——————-
3                 1
3                 259
3                 260
3                 261
3                 262
9                 279
 
(6 row(s) affected)

sys.server_role_members can be joined with sys.server_principals to retrieve role/member name:

SELECT        A.name AS 'Role', B.name AS 'Login'
FROM          sys.server_role_members C
INNER JOIN    sys.server_principals A ON A.principal_id = C.role_principal_id
INNER JOIN    sys.server_principals  B ON B.principal_id = C.member_principal_id

Result Set:

Role                 Login
——————-              ———————-
Sysadmin             sa
Sysadmin             NT AUTHORITY\SYSTEM
Sysadmin             NT SERVICE\MSSQLSERVER
Sysadmin             ***********************
Sysadmin             NT SERVICE\SQLSERVERAGENT
Dbcreator            Vishal
 
(6 row(s) affected)

sys.server_role_members contains information for server level roles, for database level roles sys.database_role_members can be used. It returns a pair of role_principal_id and member_principal_id for each membership:

SELECT role_principal_id, member_principal_id
FROM   sys.database_role_members

Result Set:

role_principal_id member_principal_id
—————–            ——————-
16384             1
16384             5
16384             6
16385             5
16385             6
16386             5
16387             5
16387             6
16389             5
16390             5
16390             6
16391             5
16391             6
 
(13 row(s) affected)

It can be joined with sys.database_principals to retrieve role/member name as below:

SELECT        A.name AS 'Role', B.name AS 'User'
FROM          sys.database_role_members C
INNER JOIN    sys.database_principals A ON A.principal_id = C.role_principal_id
INNER JOIN    sys.database_principals B ON B.principal_id = C.member_principal_id

Result Set:

Role                 User
——————–              ——————–
db_owner             dbo
db_owner             sqladmin
db_accessadmin       sqladmin
db_securityadmin     sqladmin
db_ddladmin          sqladmin
db_backupoperator    sqladmin
db_datareader        sqladmin
db_datawriter        sqladmin
db_owner             NewUser
db_accessadmin       NewUser
db_ddladmin          NewUser
db_datareader        NewUser
db_datawriter        NewUser
 
(13 row(s) affected)

Hope This Helps! Cheers!