Get List of users with roles and their permissions on each project in Jira Sever

[Diego Cañete]

I need a sql query to obtain the users with their different permissions in each project developed in Jira Server. for audit any good ideas?

Jira  Server 7.0.1

Thanks.

[John Funk]

Hi @Diego Cañete  - Did you ever get this to work? 

[Diego Cañete]

Hi John,

This report is running on our instance.
What if we mark the users that we unsubscribe by putting the email: baja@usuario

We have a report that runs every 6 months and run this query directly against the database.
Our base is in Oracle.

For you to take on account.

Any other question, available.

Greetings.

[Diego Cañete]

to clarify this point:"What if we mark the users that we unsubscribe by putting the email: "baja@usuario"

We charge this email when user accounts are disabled.

[John Funk]

Very good - thanks for sharing. 

[Diego Cañete]

I m testing this plus email <> 'baja@usuario' but I must add a condittion "project permission esqueme condittion", so any help? or suggestion? 

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
INNER JOIN cwd_user u ON u.ACTIVE = '1'
WHERE pra.roletype = 'atlassian-group-role-actor' and u.lower_email_address <> 'baja@usuario'
order by 1, 2, 3;