How to Get a List of Explicit Chat Room Members and Managers

You can use the query* below to get a list of explicit permissions assigned in Group Chat or Persistent Chat:

SELECT tblNode.nodeName AS "Category/Channel",
tblPrincipal.prinName AS "Name",
CASE tblPrincipalType.ptypeDesc
WHEN 'Parlano.Server.Common.User' THEN 'User'
WHEN 'Parlano.Server.Common.ExternalUser' THEN 'External User'
WHEN 'Parlano.Server.Common.FederatedUser' THEN 'Federated User'
ELSE tblPrincipalType.ptypeDesc
END AS "Type",
CASE isMember WHEN 1 THEN 'Y' ELSE 'N' END AS "Member",
CASE isManager WHEN 1 THEN 'Y' ELSE 'N' END AS "Manager"
FROM [GroupChat].[dbo].[Exp_RoleView]
INNER JOIN [GroupChat].[dbo].[tblPrincipal] on Exp_RoleView.principalId=tblPrincipal.prinGuid
INNER JOIN [GroupChat].[dbo].[tblPrincipalType] on tblPrincipal.prinTypeID=dbo.tblPrincipalType.ptypeID
INNER JOIN [GroupChat].[dbo].[tblNode] on Exp_RoleView.nodeDbId=tblNode.nodeID
/* WHERE isManager = 1 */
/* WHERE isMember = 1 */
ORDER BY [Category/Channel]

Note: This same query works on Lync Server 2013 Persistent Chat, however you will need to change the following line:

INNER JOIN [GroupChat].[dbo].[tblPrincipal] on Exp_RoleView.principalID=tblPrincipal.prinGuid

Remember to update the query above with the correct database name.  For GroupChat, replace [GroupChat] with the name of your GroupChat database.  For Persistent Chat, replace [GroupChat] with [mgc].

If you want only a list of members or managers, you can uncomment the appropriate line:

/* WHERE isManager = 1 */
/* WHERE isMember = 1 */

The query will produce results similar to this:

Thanks go out to Indranil Dutta for sending this my way and to Justin Kulesa and Michael Hendrix for working on the SQL query.

*These queries are provided for you to use at your own risk. Please make sure you test before running in a production environment.

Comments (4)

  1. Anonymous says:

    Ah – figured it out. There is no DB called GroupChat in greenfield Persistent Chat deployments. The DB is called mgc. Swapped out the name throughout the query and it worked fine.

  2. Anonymous says:

    When I run this query for PChat members, I’m getting:
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘GroupChat.dbo.Exp_RoleView’.

  3. dodeitte says:


    Good catch! I updated the post to better reflect that you will need to update the query with the correct database name.

  4. Michael Summers says:

    I used this on Lync 2010 Group Chat and found 1 error when it ran. On the 2nd INNER JOIN, the dbo. on dbo.tblPrincipalType.ptypeID needed to be removed to get it to work.