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.

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;