Retrieve a list of users assigned to project roles in Jira Data Center
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article presents the option to list all users by project role and project directly from the database using SQL queries.
Jira project role user list
This was written and tested using a PostgreSQL database, so you may need to tweak it depending on your database.
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;