0

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 .

3 Answers 3

1
--- 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
1

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

1
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.