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

Question

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 Answer

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