Find all users in a role

You know, HR.Net users have a primary role that defines their level of access to the data. Also there are many additional roles that allow to do other stuff, like using of navigator. But additional roles don’t give any data-permissions. That’s the way HR.Net security is done.

And sometimes you need to see all the users with a specific additional role. Here is the script:

select 
    OC_USERS.USERID, 
    primaryRole.ROLENAME as PrimaryRole
from [dbo].[OC_ROLELANGUAGES] 
    inner join OC_USERROLES on OC_ROLELANGUAGES.ROLEID = OC_USERROLES.ROLEID
    inner join OC_USERS on OC_USERS.USERID=OC_USERROLES.USERID
    inner join [OC_ROLELANGUAGES] primaryRole on primaryRole.ROLEID = Oc_users.ROLEID
where OC_ROLELANGUAGES.rolename='My Role'
order by primaryRole.ROLENAME