SQl Server查询某用户的角色

功能等同于

exec sp_help user 'user_login.name' 

由于角色可能嵌套，要用到递归查询

WITH tree_roles as

(
SELECT role_principal_id, member_principal_id
FROM sys.database_role_members
WHERE member_principal_id = USER_ID('user_login.name')
UNION ALL
SELECT c.role_principal_id,c.member_principal_id
FROM sys.database_role_members as c
inner join tree_roles
on tree_roles.member_principal_id = c.role_principal_id
)
SELECT distinct USER_NAME(role_principal_id) RoleName
FROM tree_roles