Re: How to verify whether a user has been added to a database role

Thanks Dan. Will try it.

"Dan Guzman" wrote:

Hi, Peter.

I just realize that using those views are more complicated since the user
and the desired role may be related thru a hierarchy not a direct
relationship.

The CTE below ought to identify indirect membership:

WITH role_hierarchy
AS
(
SELECT
rm.member_principal_id,
rm.role_principal_id
FROM sys.database_role_members rm
WHERE
rm.role_principal_id = USER_ID('role_name')
UNION ALL
SELECT
rm.member_principal_id,
rm.role_principal_id
FROM sys.database_role_members rm
JOIN role_hierarchy rh ON
rh.member_principal_id = rm.role_principal_id
)
SELECT 'is a member' FROM role_hierarchy rh
WHERE USER_NAME(rh.member_principal_id) = 'user_name';

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


.

Relevant Pages