i am still new in Jira and its database data structure and my SQL-knowledge is also more a beginners or it has already got rusty;
i need a sql query which gives me a list of all projects, excluding archived projects, where a certain user group (e.g. 'testgroup') has no access;
help would be appreciated;
What, exactly, do you mean by "access"? Is it just "Can read the project", or are you interested in other things (comments, edits, can progress through the workflow, etc)
Even "can see it" is not a simple SQL query. You can't answer the question without the context of the permission scheme.
If you're looking for browse access only, then the pseudocode you will need to think through is:
If you're interested in other actions, or you actually need to be looking at users, this becomes a lot more complex...
I think it might be better to question the requirement - what are you trying to do with this search for unused groups? A bit of housekeeping?
Hi @Alexander ,
Try this SQL if you have small number of projects. This SQL gives the list of project where the group is used. You can then list of remaining projects.
This was used for PostgreSQL databases and may need slight syntax adjustments depending on the DBMS Jira's database is based on.
pra.roletypeparameter AS "Group",
pr.name AS "Project Role",
p.pname AS "Project"
LEFT JOIN projectrole pr ON pra.projectroleid = pr.id
LEFT JOIN project p ON pra.pid = p.id
pra.roletype = 'atlassian-group-role-actor'
AND pra.roletypeparameter in ('helpdesk', 'administrators');
Note: Replace ('helpdesk', 'administrators') with a comma-separated list of the groups you want to check for usages.
Yes, they have.
Groups can be in notification schemes, permission schemes, global permissions, dashboards, filters etc.
All the related SQLs to find out where the groups are used is mentioned in https://confluence.atlassian.com/jirakb/how-to-identify-group-usage-in-jira-441221524.html
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event