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.
Summary
This article presents the option to list all users by project role and project directly from the database using SQL queries.
This can be specially useful for auditing, "house-cleaning" and reporting purposes.
Environment
All versions of Jira Core and Software 7.x and 8.x.
All versions of Jira Service Manager 3.x and 4.x.
Solution
This was written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.
There are three ways a user can be assigned to a project role:
Directly by username
- By a group that the user belongs to
- By a group that contains a group that contains the user (nested groups)
The query below only covers situations 1 and 2:
SELECT p.pkey as "Project key", pr.name as "Role name", u.lower_user_name as "Username", u.display_name as "Display name", u.lower_email_address as "e-Mail", null as "Group 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 pra.roletype = 'atlassian-user-role-actor'
UNION
SELECT p.pkey as "Project key", pr.name as "Role name", cmem.lower_child_name as "Username", u.display_name as "Display name", u.lower_email_address as "e-Mail", cmem.lower_parent_name as "Group 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.lower_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 1, 2, 3;The output should look something like this:
KANBAN | Administrators | admin | admin | admin@localhost | jira-administrators |
KANBAN | Administrators | mary | mary | mary@test.abc | |
KANBAN | Developers | admin | admin | admin@localhost | |
SCRUM | Administrators | admin | admin | admin@localhost | jira-administrators |
SCRUM | Developers | admin | admin | admin@localhost | |
SCRUM | Developers | clint | Clint | clint@test.abc | testgroup |
SCRUM | Developers | mary | mary | mary@test.abc | testgroup |
TEST | Administrators | admin | admin | admin@localhost | jira-administrators |
TEST | Developers | admin | admin | admin@localhost | |
KANBAN | Administrators | admin | admin | admin@localhost | jira-administrators |
KANBAN | Administrators | mary | mary | mary@test.abc | |
KANBAN | Developers | admin | admin | admin@localhost | |
SCRUM | Administrators | admin | admin | admin@localhost | jira-administrators |
SCRUM | Developers | admin | admin | admin@localhost |
When "Group name" is empty, the user is explicitly assigned to the project role, otherwise he/she inherits the role by the group membership.
Further Examples:
To get a list of all licensed users : Get list of licensed users in Jira server
1 - The following query will give you a list of projects, the roles within that project, and the users/groups assigned to that role.
SELECT p.pname as ProjN, pr.NAME as roleN, pra.roletype, pra.roletypeparameter
FROM projectroleactor pra
INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID
INNER JOIN project p ON p.ID = pra.PID
order by p.pname;The output should be similar to the following:
KANBAN | Developers | atlassian-user-role-actor | admin |
KANBAN | Administrators | atlassian-user-role-actor | JIRAUSER10101 |
KANBAN | Administrators | atlassian-group-role-actor | jira-administrators |
SCRUM | Administrators | atlassian-group-role-actor | jira-administrators |
SCRUM | Developers | atlassian-user-role-actor | admin |
SCRUM | Developers | atlassian-group-role-actor | testgroup |
test | Administrators | atlassian-group-role-actor | jira-administrators |
test | Developers | atlassian-user-role-actor | admin |
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")
2 - 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';3 - 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';
4 - 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';5 - 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;