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.