In SQL Server, roles can be nested, so that needs to be taken into account. In SQL Server 2000 you'd have to do recursion using temp tables. In SQL Server 2005 you can use a CTE. One small note, I've UNION ALLed to include public because all database principals are a member of public but it's not shown in the sys.database_role_members DMV:

WITH CTE_Roles (role_principal_id)

AS

(

SELECT role_principal_id

FROM sys.database_role_members

WHERE member_principal_id = USER_ID()

UNION ALL

SELECT drm.role_principal_id

FROM sys.database_role_members drm

INNER JOIN CTE_Roles CR

ON drm.member_principal_id = CR.role_principal_id

)

SELECT USER_NAME(role_principal_id) RoleName

FROM CTE_Roles

UNION ALL

SELECT 'public'

ORDER BY RoleName;