Project Server: Retrieve group memberships from SQL database

Use this script to retrieve the group memberships from the published database For Project Server 2013 the statement has to be slightly modified.

 
 
 
 
 
4.9 Star
(7)
Add to favorites
SharePoint
7/9/2013
E-mail Twitter del.icio.us Digg Facebook
  • ENCODED_CLAIM join doesn't show all users
    1 Posts | Last post September 06, 2018
    • Hi, For Project Server 2016, this script doesn't show all users. If I put an INNER join between MSP_WEB_SECURITY_CLAIMS and MSP_RESOURCES, joining on MSP_WEB_SECURITY_CLAIMS.ENCODED_CLAIM = MSP_RESOURCES.WRES_ACCOUNT, I only see resources that have "User can be assigned as a resource" checked in the PWA Manage Users page. I don't see other users (there are many) that can access the PWA site.
      
      Removing the INNER join and replacing with LEFT OUTER solves this problem, but then I can't get data from the RESOURCE table. Are values similar to RES_NAME, WRES_LAST_CONNECT_DATE and RES_TYPE available in another table directly linked to MSP_WEB_SECURITY_GROUP_MEMBERS or MSP_WEB_SECURITY_CLAIMS?
      
      Thank you!
  • for Project Server 2016
    1 Posts | Last post August 28, 2017
    • Dear Christoph, 
      Nice work.
      
      Please add following script for Project Server 2016.
      
      
      
      SELECT  WSG.WSEC_GRP_NAME AS [Group], R.RES_NAME AS Member 
      FROM   pjpub.MSP_WEB_SECURITY_GROUPS AS WSG 
      INNER JOIN  pjpub.MSP_WEB_SECURITY_GROUP_MEMBERS AS WSGM ON WSG.WSEC_GRP_GUID = WSGM.WSEC_GRP_GUID  
      inner join pjpub.MSP_WEB_SECURITY_CLAIMS AS WSC on WSC.SECURITY_GUID=wsgm.wres_guid 
      INNER JOIN   pjpub.MSP_RESOURCES AS R ON WSC.ENCODED_CLAIM = R.WRES_ACCOUNT 
      ORDER  BY [Group], Member
  • Any update for Project Online?
    2 Posts | Last post April 17, 2015
    • Hi,
      
      i read in the solution that is not available this information in Project Online.
      
      Any updates?
      Any solution?
      
      Thanks
      Daniele
    • Hi
      I fear this kind of Information is not available using ODATA and this is the only way to retrieve data in Project Online.
  • Is it possible to get such info from the reporting database?
    2 Posts | Last post September 13, 2013
    • If a user is having access to the reporting db only, can this info be retrieved?
    • Hi
      no. The information is not in the reporting database. You have to access the published database (or pub schema in 2013).
  • What is correct script for Project Server 2013 DB
    3 Posts | Last post July 09, 2013
    • Hi,
      I ran the script above on a Project Server 2013 DB and encounter the following error:
      Invalid column name 'RES_SECURITY_GUID'
      That field seems to no longer exist. What is the correct script?
      
      Appreciate you help.
    • The field is not available in Project Server 2013 MSP_Resources table.
    • Hi
      I added the modified script for use with Project Server 2013.