I'm using the following T-SQL to obtain role members from my SQL Server 2008 R2 database:
select rp.name as database_role, mp.name as database_user
from sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
order by rp.name
I've worked out what's going on.
When I queried out the role members I was comparing the output with what SSMS listed as role members in the role's properties dialog - this included users as well as roles, but the users weren't being listed by the query as listed in my question. I turns out that when listing role members, SSMS expands members that are roles to display the members of those roles.
The following query replicates the way in which SSMS lists role members:
WITH RoleMembers (member_principal_id, role_principal_id) AS ( SELECT rm1.member_principal_id, rm1.role_principal_id FROM sys.database_role_members rm1 (NOLOCK) UNION ALL SELECT d.member_principal_id, rm.role_principal_id FROM sys.database_role_members rm (NOLOCK) INNER JOIN RoleMembers AS d ON rm.member_principal_id = d.role_principal_id ) select distinct rp.name as database_role, mp.name as database_userl from RoleMembers drm join sys.database_principals rp on (drm.role_principal_id = rp.principal_id) join sys.database_principals mp on (drm.member_principal_id = mp.principal_id) order by rp.name
The above query uses a recursive CTE to expand a role into it's user members.