How to verify whether a user has been added to a database role?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 27 Aug 2008 00:41 GMT

I want to validate whether a user has been added to a particular database
role. I cannot use the is_member function since it only works with the
current user.

Reply to this Message

Dan Guzman - 27 Aug 2008 01:36 GMT

Try:

EXEC sp_helprolemember 'role_name_here'

In SQL 2005, you can also query from sys.database_role_members catalog view.

Signature

Hope this helps.

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

>I want to validate whether a user has been added to a particular database
> role. I cannot use the is_member function since it only works with the
> current user.

Reply to this Message

Peter - 27 Aug 2008 01:58 GMT

Hi Dan,

I actually have already tried them. The sp_helprolemember will require me
to insert the result into a temporary table and then verify.
The sys.database_role_members view will need to join with
sys.database_principals view. I wonder why there is no built-in security
function for such basic operation.

> Try:
>
[quoted text clipped - 5 lines]
> > role. I cannot use the is_member function since it only works with the
> > current user.

Reply to this Message

Dan Guzman - 27 Aug 2008 02:24 GMT

> I actually have already tried them. The sp_helprolemember will require
> me
[quoted text clipped - 3 lines]
> security
> function for such basic operation.

If you are going to use a query rather than SSMS GUI, I don't see why the
join is a big deal. In any case, you can avoid the JOIN by using other
security functions:

SELECT
USER_NAME(rm.member_principal_id) AS UserName,
USER_NAME(rm.role_principal_id) AS RoleName
FROM sys.database_role_members rm
WHERE
rm.member_principal_id = USER_ID('some_user')
AND rm.role_principal_id = USER_ID('some_role')

Signature

Hope this helps.

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

Reply to this Message

Peter - 27 Aug 2008 02:24 GMT

Hi Dan,

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.

> Try:
>
[quoted text clipped - 5 lines]
> > role. I cannot use the is_member function since it only works with the
> > current user.

Reply to this Message

Dan Guzman - 27 Aug 2008 03:02 GMT

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';

Signature

Hope this helps.

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

Reply to this Message

Peter - 27 Aug 2008 03:31 GMT

Thanks Dan. Will try it.

> Hi, Peter.
>
[quoted text clipped - 23 lines]
> SELECT 'is a member' FROM role_hierarchy rh
> WHERE USER_NAME(rh.member_principal_id) = 'user_name';

Reply to this Message

Peter - 28 Aug 2008 19:45 GMT

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

> Hi, Peter.
>
[quoted text clipped - 23 lines]
> SELECT 'is a member' FROM role_hierarchy rh
> WHERE USER_NAME(rh.member_principal_id) = 'user_name';

Reply to this Message

Dan Guzman - 29 Aug 2008 02:02 GMT

> Does it look like the correct way to implement the function with the CTE?

Yes, I think your function will return the desired result.

Signature

Hope this helps.

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

> Hi Dan,
>
[quoted text clipped - 61 lines]
>> SELECT 'is a member' FROM role_hierarchy rh
>> WHERE USER_NAME(rh.member_principal_id) = 'user_name';

Reply to this Message

Erland Sommarskog - 30 Aug 2008 12:03 GMT

> I want to validate whether a user has been added to a particular database
> role. I cannot use the is_member function since it only works with the
> current user.

In additions to Dan's suggestion, you can:

EXECUTE AS LOGIN = 'thatuser'
go
SELECT is_member('somerole')
go
REVERT

I think this is better than running queries as Dan suggested, particularly
in the case that user may be have role membership through a Windows
domain.

If you think that there should be a function to retrieve permissions
or role membership with going through impersonation - which is impractical
if you want to check a suite of users - submit a suggestion on
http://connect.microsoft.com/SqlServer/Feedback. (I wouldn't be surprised
if there is such a suggestion already.)

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message