Are you wondering who has project admin access on your JIRA or Confluence and you got something else to do than spending your afternoon clicking through all the permission schemes, and ‘Users and Roles’ screens?

Here are 2 simple SQL queries which extract the list of users with this type of god power.

Extract all users who have project administration access on JIRA:

SELECT distinct cu.display_name , cu.email_address
FROM dbo.schemepermissions sp
inner join cwd_membership cm on cm.lower_parent_name = sp.perm_parameter
inner join cwd_user cu on cm.lower_child_name = cu.lower_user_name
where sp.PERMISSION_KEY = 'ADMINISTER_PROJECTS'
and cu.active = 1
union
SELECT distinct cu.display_name
, cu.email_address
FROM dbo.schemepermissions sp
inner join cwd_user cu on cu.lower_user_name = sp.perm_parameter
where sp.PERMISSION_KEY = 'ADMINISTER_PROJECTS'
and cu.active = 1

Extract all users who have space administration access on confluence:

SELECT sp.SPACENAME, cu.lower_display_name,cu.lower_email_address
FROM SPACES sp
JOIN SPACEPERMISSIONS pm ON sp.SPACEID = pm.SPACEID
JOIN user_mapping um ON pm.PERMUSERNAME = um.user_key
JOIN confluencedb.dbo.cwd_user cu on um.lower_username = cu.lower_user_name
WHERE pm.PERMTYPE = 'SETSPACEPERMISSIONS'
AND sp.SPACETYPE = 'global'
ORDER BY sp.SPACEKEY

Are you still using multiple-issue trackers?

Check out Exalate – It’s an advanced issue synchronization solution that supports cross-organization issue tracking.
Its flexibility comes from the built-in groovy scripting capability, allowing the implementation of almost any type of integration.

Do you need support?

You can always contact us to help out with the implementation of your use cases.