I had to do this just recently. Here is what I used to get a list of users and all the roles they belong to. They are listed more than once if they belong to more than one role.

-- set database context first

select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleName

from sys.database_principals members

inner join sys.database_role_members drm

on members.principal_id = drm.member_principal_id

inner join sys.database_principals roles

on drm.role_principal_id = roles.principal_id

WHERE members.name <> 'dbo'

ORDER BY members.name