Retrieve a list of users assigned to project roles in Jira server
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
The following query will give you a list of projects, the roles within that project, and the users assigned to that role.
This was written in MySQL syntax, so you may need to tweak it depending on the database you are using
This was written for JIRA schemas above v6.0
SELECT p.pname, pr.NAME, u.display_name FROM projectroleactor pra INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID INNER JOIN project p ON p.ID = pra.PID INNER JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name;
This is a pretty basic query, so you will probably have to alter it a bit to get the exact results you want. For example, you can add some criteria to limit the results by Project (where p.pname = "My Project") or by role (pr.NAME = "Developers")
// List of users belonging to groups assigned to project roles for all projects SELECT p.pname as ProjN, pr.NAME as roleN, pra.roletype, pra.roletypeparameter, cmem.child_name, u.display_name FROM projectroleactor pra INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID INNER JOIN project p ON p.ID = pra.PID INNER JOIN cwd_membership cmem ON cmem.parent_name = pra.roletypeparameter INNER JOIN app_user au ON au.lower_user_name = cmem.child_name INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name WHERE pra.roletype = 'atlassian-group-role-actor' order by p.pname;