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

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.

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

--
Hope this helps.

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

.