none
How to retrive all users who are using Project Professional 2010 RRS feed

  • Question

  • Hi,

    I want to know who all users are using Project Professional with respect to the permissions in Project Server.

    What are the ways to find out and retrieve all users.


    Ram

    Monday, October 21, 2013 7:38 AM

Answers

  • hello,

    A supported way would be to use the PSI, alternatively and unsupported way is by querying the Published database directly with T-SQL. I would recommend taking a backup of the Published database, restoring it to another SQL Server / restore it as a new databases with a new name and run the query against that new copy rather than the live database that way it is fully supported :)

    The query is below

    SELECT
             r.RES_NAME,
             wc.Conv_string 
                     FROM
             dbo.MSP_RESOURCES AS r
                INNER JOIN dbo.MSP_WEB_SECURITY_GROUP_MEMBERS AS GM ON GM.WRES_GUID = r.RES_SECURITY_GUID
                INNER JOIN dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS AS cr ON cr.WSEC_SP_GUID = GM.WSEC_GRP_GUID AND cr.WSEC_CAT_UID IS NULL
                INNER JOIN dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS AS cp ON cp.WSEC_REL_UID = cr.WSEC_REL_UID
                INNER JOIN dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS AS FA on FA.WSEC_FEA_ACT_UID = cp.WSEC_FEA_ACT_UID
                INNER JOIN dbo.MSP_WEB_CONVERSIONS as WC on WC.CONV_VALUE = FA.WSEC_FEA_ACT_NAME_ID
                where  cp.WSEC_FEA_ACT_UID in ('D2F88263-31C5-4FF5-BEAE-B98B26C5C116') 
                and cp.WSEC_ALLOW = 1

    Let new know if this is what you were looking for.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    • Marked as answer by Rams16 Wednesday, October 30, 2013 7:31 AM
    Friday, October 25, 2013 4:36 PM
    Moderator

All replies

  • hello,

    A supported way would be to use the PSI, alternatively and unsupported way is by querying the Published database directly with T-SQL. I would recommend taking a backup of the Published database, restoring it to another SQL Server / restore it as a new databases with a new name and run the query against that new copy rather than the live database that way it is fully supported :)

    The query is below

    SELECT
             r.RES_NAME,
             wc.Conv_string 
                     FROM
             dbo.MSP_RESOURCES AS r
                INNER JOIN dbo.MSP_WEB_SECURITY_GROUP_MEMBERS AS GM ON GM.WRES_GUID = r.RES_SECURITY_GUID
                INNER JOIN dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS AS cr ON cr.WSEC_SP_GUID = GM.WSEC_GRP_GUID AND cr.WSEC_CAT_UID IS NULL
                INNER JOIN dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS AS cp ON cp.WSEC_REL_UID = cr.WSEC_REL_UID
                INNER JOIN dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS AS FA on FA.WSEC_FEA_ACT_UID = cp.WSEC_FEA_ACT_UID
                INNER JOIN dbo.MSP_WEB_CONVERSIONS as WC on WC.CONV_VALUE = FA.WSEC_FEA_ACT_NAME_ID
                where  cp.WSEC_FEA_ACT_UID in ('D2F88263-31C5-4FF5-BEAE-B98B26C5C116') 
                and cp.WSEC_ALLOW = 1

    Let new know if this is what you were looking for.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    • Marked as answer by Rams16 Wednesday, October 30, 2013 7:31 AM
    Friday, October 25, 2013 4:36 PM
    Moderator
  • In addition to what paul said, with 2007 there was a report pack II released(http://archive.msdn.microsoft.com/EPMReportPack/Release/ProjectReleases.aspx?ReleaseId=3114) which contained references to security groups and categories and relevant entities as administrative reports, and since for 2010 DB schema hasn't changed, it should be a good starting point to look if you still want to get the data from reporting DB


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    Tuesday, October 29, 2013 2:56 PM
    Moderator