How to get a list of projects that have Anonymous Access in Jira from Database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The information in this page relates to SQL queries for reporting purpose. Atlassian Support cannot guarantee to provide any support for the steps described on this page as using SQL for business intelligence is beyond the scope of Atlassian Support Offerings. Please be aware that this material is provided for your information only and that you use it at your own risk. Please try the workaround/resolution on your staging/development/testing Jira instance database before performing any changes on production Jira instance database.
The purpose of this article is to show how to retrieve a list of projects that have Anonymous Access, which means Browse Project permission is granted to the group Anyone on the web. There are multiple tables involved in the SQL queries to get this information.
Solution
Workaround
Log into the Jira database
Run the following SQL command:
1 2 3 4 5 6 7 8 9 10 11 12SELECT p.id, p.pname, ps.name FROM project p INNER JOIN nodeassociation na ON p.id = na.source_node_id INNER JOIN schemepermissions sp ON na.sink_node_id = sp.scheme INNER JOIN permissionscheme ps ON na.sink_node_id = ps.id WHERE na.source_node_entity = 'Project' AND na.sink_node_entity = 'PermissionScheme' AND sp.permission_key='BROWSE_PROJECTS' AND sp.perm_type='group' AND sp.perm_parameter is nullℹ️ This SQL is only tested on PostgreSQL. For other database, the SQL may need to be altered to suit the syntax.
The result would be, for example:
1 2 3 4 5id | pname | name -------+-------+------------------------------------------------------ 10200 | MOGA1 | Default Permission Scheme 10000 | TEST | Default Permission Scheme 10101 | SD2 | Jira Service Management Permission Scheme for Project SDT
Was this helpful?