How to list all users by project role in Jira

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.

 

Summary

This article presents a SQL way to list all users by project role and project.

This is 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

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:

 Project key |     Role name     | Username | Display name  |          e-Mail          |       Group name       
-------------+-------------------+----------+---------------+--------------------------+------------------------
 BIS         | Administrators    | admin    | Administrator | abcdefghij@klmnopqrs.com | jira-administrators
 ITSD        | Administrators    | admin    | Administrator | abcdefghij@klmnopqrs.com | 
 ITSD        | Service Desk Team | admin    | Administrator | abcdefghij@klmnopqrs.com | jira-servicedesk-users

When "Group name" is empty, the user is explicitly assigned to the project role, otherwise he/she inherits the role by the group membership.


Last modified on Jan 13, 2021

Was this helpful?

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