How to get all roles for more than 100 particular uers?

Jose Lezana

I would need a query get to all roles for each Jira user.

I'm using Jira v7.12.3. The ouput will need to be  doccumented for audit porpouse.

Thanks,

José.- 

Petter Gonçalves

Hello Jose,

Thank you for contacting Atlassian Community!

The project roles assigned to each user will depend on the projects you are referring to.

Also, a user can be added to a project role directly or by belonging to a group that was added to the project role.

To return a list of projects, the roles within that project, and the users assigned to that role, you can use the following query:

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.lower_user_name = pra.ROLETYPEPARAMETER
INNER JOIN cwd_user u ON u.user_name = au.user_key;

Now, if you would like to have a list of users belonging to groups assigned to project roles for all projects, you can do the following database query:

<span>SELECT</span> p<span>.</span>pname <span>as</span> ProjN<span>,</span> pr<span>.</span>NAME <span>as</span> roleN<span>,</span> pra<span>.</span>roletype<span>,</span> pra<span>.</span>roletypeparameter<span>,</span> cmem<span>.</span>child_name<span>,</span> u<span>.</span>display_name
<span>FROM</span> projectroleactor pra
<span>INNER</span> <span>JOIN</span> projectrole pr <span>ON</span> pr<span>.</span>ID <span>=</span> pra<span>.</span>PROJECTROLEID
<span>INNER</span> <span>JOIN</span> project p <span>ON</span> p<span>.</span>ID <span>=</span> pra<span>.</span>PID
<span>INNER</span> <span>JOIN</span> cwd_membership cmem <span>ON</span> lower_parent_name<span>=</span>pra<span>.</span>roletypeparameter
<span>INNER</span> <span>JOIN</span> app_user au <span>ON</span> au<span>.</span>lower_user_name <span>=</span> cmem<span>.</span>child_name
<span>INNER</span> <span>JOIN</span> cwd_user u <span>ON</span> u<span>.</span>user_name <span>=</span> au<span>.</span>user_key
<span>WHERE</span> pra<span>.</span>roletype <span>=</span> <span>'atlassian-group-role-actor'</span> <span>order</span> <span>by</span> p<span>.</span>pname<span>;</span>

You can refer to the documentation below to have more information about it:

How to get a list of users assigned to project roles for your project

 Let me know if this information helps.