功能等同于
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
阅读(3835) | 评论(0) | 转发(0) |