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.ptypeDescWHEN 'Parlano.Server.Common.User' THEN 'User'WHEN 'Parlano.Server.Common.ExternalUser' THEN 'External User'WHEN 'Parlano.Server.Common.FederatedUser' THEN 'Federated User'ELSE tblPrincipalType.ptypeDescEND 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.prinGuidINNER JOIN [GroupChat].[dbo].[tblPrincipalType] on tblPrincipal.prinTypeID=dbo.tblPrincipalType.ptypeIDINNER 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.
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'.
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.
Good catch! I updated the post to better reflect that you will need to update the query with the correct database name.
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.Thanks.