Quick snippet I put together for reviewing basic info on database users/principals, permissions, and members if the principal is a role.

/******************************************************* Some Basic Info on Database principals, permissions,  explicit permissions, and if role, who is in this role currently *******************************************************/ ; with  roleMembers     as (            select              drm.role_principal_id               ,dp.principal_id                ,dp.name            from                sys.database_role_members drm               inner join sys.database_principals dp               on drm.member_principal_id = dp.principal_id        )  select   db_name()   ,dp.name    ,general_permissions = stuff    ( (         select distinct             ', ' + permission_name      from            sys.database_permissions p      where           dp.principal_id = p.grantee_principal_id            and p.major_id = 0          and p.state = 'G'       for xml path (''), type     )   .value( '.', 'varchar(max)' ), 1, 1, '' )   ,deny_permissions =     stuff   ( (         select distinct             ', ' + permission_name      from            sys.database_permissions p      where           dp.principal_id = p.grantee_principal_id            and p.major_id = 0          and p.state = 'D'       for xml path (''), type     )   .value( '.', 'varchar(max)' ), 1, 1, '' )   ,specific_permissions =     stuff   ( (         select distinct             ', ' + permission_name + ' on ' + object_schema_name( p.major_id ) + '.' + object_name( p.major_id )        from            sys.database_permissions p      where           dp.principal_id = p.grantee_principal_id            and major_id <> 0         for xml path (''), type     )   .value( '.', 'varchar(max)' ), 1, 1, '' )   ,current_active_members =   stuff   ( (         select distinct             ', ' + r.name       from            roleMembers r       where           r.role_principal_id = dp.principal_id       for xml path (''), type     )   .value( '.', 'varchar(max)' ), 1, 1, '' ) from  sys.database_principals dp order by     dp.name asc