How to find out what communities a user owns
Today there was a question in the Connections on Premises community where a company wanted to know which communities one of their Connections admins, who had left the company, owned. There’s not much special here about this person being a Connections admin, except that a Connections admin usually owns *a lot* of communities. The question therefore could be more generic to, how to find out what communities and files a user owns.
For normal users, to get a list of communities they are a member of, there’s a wsadmin command to answer this question:
This gives a list of all communities this user is a member of. It can be used, but due to the fact that the community description is also in there, it’s actually not that useful. If you have a user that’s a member of a really huge amount of communities you might get this message:
WASX7015E: Exception running command: "CommunitiesService.fetchCommByMember("email@example.com")"; exception information: javax.management.RuntimeMBeanException java.lang.RuntimeException: java.lang.RuntimeException: com.ibatis.dao.client.DaoException: java.sql.SQLException: Error: executeQueryForObject returned too many results
The way around both problems is to query the SQL database directly:
In the SNCOMM Database:
SELECT SNCOMM.MEMBER.COMMUNITY_UUID AS COMMUNITY_UUID, SNCOMM.COMMUNITY.NAME AS COMMUNITY_NAME, SNCOMM.MEMBERPROFILE.DISPLAY AS MEMBER_NAME, CASE SNCOMM.MEMBER.ROLE WHEN 0 THEN 'Member' WHEN 1 THEN 'Owner' END AS MEMBER_ROLE FROM SNCOMM.MEMBER LEFT OUTER JOIN SNCOMM.MEMBERPROFILE ON SNCOMM.MEMBER.MEMBER_UUID = SNCOMM.MEMBERPROFILE.MEMBER_UUID LEFT OUTER JOIN SNCOMM.COMMUNITY ON SNCOMM.COMMUNITY.COMMUNITY_UUID = SNCOMM.MEMBER.COMMUNITY_UUID WHERE SNCOMM.MEMBERPROFILE.DISPLAY LIKE 'Martijn%Jong'
This outputs a table with the community id, Community name, Display name of the user and whether the user is a member or owner of this community. Thanks to Sharon James for the question and Martin Leyrer for the initial query.