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 · Accepted solution]

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:

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 lower_parent_name=pra.roletypeparameter
INNER JOIN app_user au ON au.lower_user_name = cmem.child_name
INNER JOIN cwd_user u ON u.user_name = au.user_key
WHERE pra.roletype = 'atlassian-group-role-actor' order by p.pname;

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.

[Jose Lezana]

Thank so much Peterson for your reply.

But how would I have at least all groups names assigned to a particular user name?

Thanks,

José.-

[Petter Gonçalves]

Hello Jose,

you can use the following query:

select parent_name from cwd_membership where child_name = 'username1'

Where username1 is the username of the user that you would like to return the related groups.

Please, let me know if it works.

[Jose Lezana]

It works like a charm!  

Thanks so much and happy new year!!

[Petter Gonçalves]

I'm glad to know I was able to help, Jose.

I wish a happy new year for you and your loved ones too!