Project Server Security Mode / Reading user's group assignments

ellizsillva22Sorry, no clean solution here. We are using Project Server Security Model and read from the following SQL statement:

SELECT m.SiteID, g.WSEC_GRP_NAME, m.WRES_GUID, r.RES_NAME
  FROM pjpub.MSP_WEB_SECURITY_GROUP_MEMBERS AS m
       LEFT JOIN pjpub.MSP_RESOURCES AS r
	           ON (    (r.SiteId = m.SiteID)
			       AND (pjpub.MSP_WEB_FN_SEC_GetUserSecurityGuid(m.SiteId, r.RES_UID) = m.WRES_GUID))
	   INNER JOIN pjpub.MSP_WEB_SECURITY_GROUPS AS g
	           ON (    (g.SiteId = m.SiteId)
			       AND (g.WSEC_GRP_GUID = m.WSEC_GRP_GUID))

This would retrieve all group memberships and needs to be reduced to select only the resource in question, e.g. by adding a condition to the JOIN on MSP_RESOURCES to filter resources by name, e-mail address or similar.

To keep this at least a bit safe, we are not running SQL connections directly from the clients to SQL Server but employ an ASP.NET web service providing a REST service to run the query with the user's name as a parameter, returning the list of security groups as a JSON document, so the actual SQL is hidden from users, and not much harm can be done by executing the REST endpoint.

Sorry but no better solution was possible.

Cheers,

Joe