Does there exist a user with this role?

I’m trying to write a query which will return a boolean value if there exists any user which has this specific role.

For example is there any user hwo’s role is ‘role1‘

This is how far i got

SELECT CASE WHEN EXISTS 
(SELECT --'Here i don't know how to select all user's which have a specific role'--    )
GO

And if it’s possible to check user’s from only one database .

--- Declare Variable to set Boolean value
DECLARE @UserExist BIT = 0 
DECLARE @RoleName varchar(50) = 'db_datareader'


--- Set Bit to 1 if any user is member of Role 'db_datareader'
SELECT  @UserExist = 1
FROM    sys.database_role_members
WHERE   IS_ROLEMEMBER( @RoleName , USER_NAME(role_principal_id)) = 1


--- Get Output 
SELECT  @UserExist

It’s old code, but it checks out:

SELECT 
    user_name(member_principal_id) as memberName
FROM
    sys.database_role_members
WHERE 
    user_name(role_principal_id) = 'db_owner'

So, in full:

    DECLARE @roleName varchar(20) = 'MF_Pilots'
    DECLARE @user varchar (20) = 'Rey'
SELECT CASE WHEN EXISTS
    (SELECT 
    user_name(member_principal_id) as memberName
FROM
    sys.database_role_members
WHERE 
    user_name(role_principal_id) = @roleName
AND 
    user_name(member_principal_id) = @user)
    THEN 1 
    ELSE 0
    END

That’ll do what you want. Stick it in a function and profit.

Taken from https://stackoverflow.com/questions/3361778/get-list-of-all-database-users-with-specified-role

SELECT IS_ROLEMEMBER('db_datareader','guest');

Will return 1 if the guest user is a member of the db_datareader database role and will return 0 otherwise.