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;The output should be similar to the following:
pname name display_name
-------------- ----------------- ------------
SCRUM Developers admin
KANBAN Developers admin
Justice League Administrators admin
Terraria Administrators admin
Justice League Service Desk Team Thor
Terraria Service Desk Team Loki
KANBAN Developers Loki 
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")
Further Examples:
Filtering by Role name:
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
WHERE name = 'Service Desk Team';Filtering by Project Name:
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
WHERE pname = 'SCRUM';
Filtering by Jira Service Management projects only(To search for Jira Software, just change to Software):
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
WHERE projecttype = 'service_desk';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;
List the admin and project rules and when a particular user was added to the Project role (by joining the audit_log and audit_item tables):
SELECT al.created as Created_Date,al.author_key as Change_Author, ai.object_name as Project_Name, al.object_name as Project_Role,acv.delta_from, acv.delta_to
FROM audit_log al
INNER JOIN audit_item ai on al.id = ai.log_id and al.summary = 'Project roles changed'
INNER JOIN audit_changed_value acv on acv.log_id = al.id
WHERE al.created > '2019-05-31'
ORDER BY al.created,ai.object_name, al.object_name;