none
SQL Query for Project Permission in Project Server 2010 RRS feed

  • Question

  • HI

    I have assign multiple users on multiple projects with different level permission, 

    The permission level is assign from Project Permission (icon ribbon in PWA 2010.)

    (1.Open the project within Project Professional or Project Web App,

    2.Edit and Save the project within Project Professional or Project Web App

    3.Edit Project Summary Fields within Project Professional or Project Web App

    4.Publish the project within Project Professional or Project Web App

    5.View the Project Summary in the Project Center

    6.View the Project Schedule Details in Project Web App.

    7.View the Project Site)

    .

    Is there any SQL Query to find which user/resource is assign to which projects and what above permission is assign.

    Thanks 

    Farhan



    farhan





    • Edited by Farhan_123 Monday, December 2, 2013 7:31 PM
    Monday, December 2, 2013 5:42 PM

Answers

All replies

  • Hi Farhan,

    I am not sure that this is exactly what you are wanting but this should point you in the right direction for Project level permissions.  NOTE - using the published database for queries is not recommended and not supported by Microsoft.


    Collin

    • Proposed as answer by Collin Quiring Friday, December 6, 2013 6:50 PM
    Wednesday, December 4, 2013 2:08 PM
  • Hi Collin

    Since i have provided access to multiple users on single Project and vica versa with different Level of Project Permissions(as we known there are seven type of Project level permission) 

    I would like to know the SQL query for which user is assign to which Project with what Project permissions.

    Example as below:

    Thks

    Farhan


    farhan

    Wednesday, December 4, 2013 4:48 PM
  • Any SQL Query for above query

    farhan

    Thursday, December 5, 2013 4:28 PM
  • http://pmpspecialists.com/Blog/2013/10/resource-permission-queries-in-project-server/

    Sorry, this is the link I tried to send the first time.  Take a look and see if this gives you what you need.


    Collin

    • Marked as answer by Farhan_123 Friday, December 6, 2013 5:47 PM
    • Unmarked as answer by Farhan_123 Friday, December 6, 2013 5:47 PM
    • Marked as answer by Farhan_123 Friday, December 6, 2013 5:49 PM
    Thursday, December 5, 2013 6:27 PM
  • Collin

    Thanks a lot

    I enhance your SQL statement, through which i able to get my requirement.


    farhan

    Friday, December 6, 2013 5:49 PM
  • Hi Farhan,

    Please find the SQL below below which will give project permission users:

    select RES_SECURITY_GUID from ProjectServer_Published.dbo.MSP_RESOURCES where RES_UID = '37A56C30-34DE-417B-95A8-42FBA6F47565'

    select PROJ_NAME  from ProjectServer_Published.dbo.MSP_PROJECTS where PROJ_UID in (
    select distinct c.PROJ_UID from ProjectServer_Published.dbo.MSP_WEB_SECURITY_PROJECT_CATEGORIES c
              INNER JOIN ProjectServer_Published.dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS pcr on pcr.WSEC_CAT_UID = c.WSEC_CAT_UID
              INNER JOIN ProjectServer_Published.dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS pcp on pcp.WSEC_REL_UID = pcr.WSEC_REL_UID
              INNER JOIN ProjectServer_Published.dbo.MSP_WEB_SECURITY_GROUP_MEMBERS gm on gm.WSEC_GRP_GUID = pcr.WSEC_SP_GUID
              INNER JOIN ProjectServer_Published.dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS AS SFA ON SFA.WSEC_FEA_ACT_UID = pcp.WSEC_FEA_ACT_UID
              INNER JOIN ProjectServer_Published.dbo.MSP_WEB_CONVERSIONS AS CONV ON CONV.CONV_VALUE = SFA.WSEC_FEA_ACT_NAME_ID where (1=1)
    And (CONV.LANG_ID = 1033) and gm.WRES_GUID = '2F5512D4-F561-43AF-AB7B-A189B0B9B6E8') -- Replace with above returned RES_SECURITY_GUID

    Vivek

    Wednesday, December 18, 2013 5:16 AM
  • I m loking same,above query did not give result.

    Hasan Jamal Siddiqui(MCTS,MCPD,ITIL@V3),Sharepoint and EPM Consultant,TCS

    Thursday, August 25, 2016 8:40 AM