Retrieve a list of users assigned to project roles in Jira server

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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.

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:

  1. Directly by username

  2. By a group that the user belongs to
  3. 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

(warning) 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;



Last modified on Nov 7, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.