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