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

Hi Dan,

The CTE works. I have never used CTE before. I create the following user
function temporarily to see how I can use the CTE in a function. I just have
the parameter for the user but will add the parameter for the role later.
Does it look like the correct way to implement the function with the CTE?
Thanks.

CREATE function [dbo].[check_role](@user varchar(100))
returns bit
as begin
declare @bexist bit
select @bexist = 0;
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('SQLAgentUserRole')
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 @bexist = 1 FROM role_hierarchy rh
WHERE USER_NAME(rh.member_principal_id) = @user
return @bexist
end

"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