SQL Query to return which Projects a user is explicitly listed in

[Helen Griffiths]

Good afternoon

I am looking for a bit of help to write a SQL query which will return which JIRA projects a user is explicitly listed in within Roles.

Any help would be appreciated :) 

[Andy Heinzer · Accepted solution]

Hi Helen,

There is a KB for this over in How to get a list of users assigned to project roles for your project.  I believe you can use the SQL query of:

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;

in order to retun all the usernames that have been explicitly assigned a role in a project in JIRA.   In my own testing this does not include groups that have been assigned to a role that users might be a member of, so I think this should be able to provide you with a list of the explicit user accounts added to specific project roles.

[Helen Griffiths]

Good morning @Andy Heinzer

You're absolutely right. I found the KB article not long after posting this but forgot to update to say i'd found it.

The SQL works perfectly, I did a bit of an addition at the end to search for a key word within the account name and it did exactly what I wanted it to do.

Thank you for coming back to me, it's very much appreciated :)

[Bridy Frett]

Hi,
I'm using the query above (from the confluence link), but I've found a couple scenarios where active users assigned a project role aren't showing in the query results. Other users in the same project / project role are showing in the query results.
Out of 3,000 users, I know of 2 users who aren't in the output of this query. I'm not sure how many other users are missing in the output.
Has anybody else run into this issue?
Thanks!
Bridy