none
Project Server 2007 - Users' security groups RRS feed

  • Question

  • We would like to see all Users and their Security groups they belong top.  Is there any view available for this? If not, how to get this information?
    Sanjeev
    Thursday, February 17, 2011 6:08 PM

Answers

  • Uuups, forgot to paste the query - remember, queries on Published DBs are not supported!

    SELECT     dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME
    FROM         dbo.MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
                          dbo.MSP_WEB_SECURITY_GROUPS ON
                          dbo.MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
                          dbo.MSP_RESOURCES ON dbo.MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = dbo.MSP_RESOURCES.RES_SECURITY_GUID
    ORDER BY dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME

    Regards
    Barbara

    • Marked as answer by Sanjeev S K Thursday, February 17, 2011 6:25 PM
    Thursday, February 17, 2011 6:22 PM
    Moderator

All replies

  • Hi Sanjeev,

    I am not not aware of any solution in PWA. You could create a report for that, but this information is not available in Reporting DB. You can find this information in Published DB, but queries on this DB are not supported.

    Regards
    Barbara

    Thursday, February 17, 2011 6:20 PM
    Moderator
  • Uuups, forgot to paste the query - remember, queries on Published DBs are not supported!

    SELECT     dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME
    FROM         dbo.MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
                          dbo.MSP_WEB_SECURITY_GROUPS ON
                          dbo.MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
                          dbo.MSP_RESOURCES ON dbo.MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = dbo.MSP_RESOURCES.RES_SECURITY_GUID
    ORDER BY dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME

    Regards
    Barbara

    • Marked as answer by Sanjeev S K Thursday, February 17, 2011 6:25 PM
    Thursday, February 17, 2011 6:22 PM
    Moderator
  • Uuups, forgot to paste the query - remember, queries on Published DBs are not supported!

    SELECT     dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME
    FROM         dbo.MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
                          dbo.MSP_WEB_SECURITY_GROUPS ON
                          dbo.MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
                          dbo.MSP_RESOURCES ON dbo.MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = dbo.MSP_RESOURCES.RES_SECURITY_GUID
    ORDER BY dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME

    Regards
    Barbara

    Hi Barbara,

    We are in the process of migrating to Project Server 2013. While trying to execute the SQL script above on the Project Server 2013 DB by replacing 'dbo' with 'pub', I received the following error:

    Invalid column name 'RES_SECURITY_GUID'

    Looking at the pub.MSP_RESOURCES table, the field does not exist any more.

    Could you advise the SQL script to obtain the same info?

    KH

    Thursday, June 27, 2013 9:08 AM
  • For 2013

    pub.MSP_WEB_SECURITY_GROUPS AS WSG INNER JOIN

                      pub.MSP_WEB_SECURITY_GROUP_MEMBERS AS WSGM ON WSG.WSEC_GRP_GUID = WSGM.WSEC_GRP_GUID  inner join 
     pub.MSP_WEB_SECURITY_CLAIMS AS WSC on WSC.SECURITY_GUID=wsgm.wres_guid INNER JOIN   
     pub.MSP_RESOURCES AS RP ON WSC.ENCODED_CLAIM = RP.WRES_ACCOUNT LEFT OUTER JOIN
                      MSP_EpmResource_UserView AS RR ON RR.ResourceUID = RP.RES_UID

    Friday, January 17, 2014 3:30 PM