select
    r.name
    , user_name     = p.name
    , login_name    = sp.name
from        sys.database_role_members   m
inner join  sys.database_principals     p   on m.member_principal_id    = p.principal_id
inner join  sys.database_principals     r   on m.role_principal_id      = r.principal_id
left join   sys.server_principals       sp  on p.sid                    = sp.sid
where
    sp.name = '<login_name>'