The sys.database_role_members view contains two columns one for the role id and one for the member id creating a relationship between the role and the user. When we join this data to the sys.database_principals view we can retrieve the friendly name of the role and using another join to get the User name.
This will only return roles and the users associated if the role is not empty of members.
select rp.name as 'Role Name', mp.name as 'User' from sys.database_role_members rm
inner join sys.database_principals rp on rm.role_principal_id = rp.principal_id
inner join sys.database_principals mp on rm.member_principal_id = mp.principal_id