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
Will return 1 if the guest user is a member of the db_datareader database role and will return 0 otherwise.