How do I find "illegal" users in "Users and roles"?

[DWedekind]

We assign permissions in Jira via memberships in AD groups. Individual users listed by name must not exist under "Users and Roles".
Is there a (simple) way to find such "violations" in over 160 projects?

Thanks in advance for your input!

[Rilwan Ahmed · Accepted solution]

Hi @DWedekind 
Please run this query in DB:

SELECT p.pname, p.pkey, 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.user_key = pra.ROLETYPEPARAMETER
INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name;

More details in https://confluence.atlassian.com/jirakb/retrieve-a-list-of-users-assigned-to-project-roles-in-jira-server-705954232.html

[DWedekind]

Thanks. Works like a charm :-)