none
Need info for resource table RRS feed

  • Question

  • Hi

       I am the Administrator for Project Server 2010. One of the user requested that total no of resources and their permissions.

     Total no of resources i took from MSP_Resources table (Publish Database)

    My question is how to retrive the permission for each corresponding resources (name of the group)

    Note:  We are working only with Groups

    So i would like to know resources belongs to which group

    Let me know anything else you need to from my side

     

    Regards

    Santosh

    Monday, December 17, 2012 7:29 AM

Answers

  • Hi,

    Here you go, please test in your test environment first and also replace the published database name with your one.

    SELECT 
    MSP_Resources.RES_NAME,
    MSP_WEB_SECURITY_GROUPS.WSEC_GRP_Name AS [Group Name]

    FROM
    MSP_EpmResource_UserView ruv

    inner join

    pwa_Published.dbo.MSP_Resources
    on ruv.ResourceUID=MSP_Resources.res_uid
    Inner JOIN
    pwa_Published.dbo.MSP_WEB_SECURITY_GROUP_MEMBERS
    on MSP_Resources.RES_SECURITY_GUID = MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID
    inner JOIN
    pwa_Published.dbo.MSP_WEB_SECURITY_GROUPS
    on MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID

    group by
    MSP_WEB_SECURITY_GROUPS.WSEC_GRP_Name,
    MSP_Resources.RES_NAME


    Regards, Naweed

    Monday, December 17, 2012 3:31 PM

All replies

  • Hi,

    Here you go, please test in your test environment first and also replace the published database name with your one.

    SELECT 
    MSP_Resources.RES_NAME,
    MSP_WEB_SECURITY_GROUPS.WSEC_GRP_Name AS [Group Name]

    FROM
    MSP_EpmResource_UserView ruv

    inner join

    pwa_Published.dbo.MSP_Resources
    on ruv.ResourceUID=MSP_Resources.res_uid
    Inner JOIN
    pwa_Published.dbo.MSP_WEB_SECURITY_GROUP_MEMBERS
    on MSP_Resources.RES_SECURITY_GUID = MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID
    inner JOIN
    pwa_Published.dbo.MSP_WEB_SECURITY_GROUPS
    on MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID

    group by
    MSP_WEB_SECURITY_GROUPS.WSEC_GRP_Name,
    MSP_Resources.RES_NAME


    Regards, Naweed

    Monday, December 17, 2012 3:31 PM
  • Hi Naweed,

    MSP_EpmResource_UserView  view is only in Reporting database. Above query can be execute on Reporting database only after replacing Published database name.

    Hope this helps


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful; if a post has been useful to you.This can be beneficial to other community members reading the thread.


    Monday, December 17, 2012 11:30 PM
    Moderator
  • Thanks a lot

    I got what i need

    Regards

    Santosh

    Tuesday, December 18, 2012 11:37 AM