locked
Need Logic for Viewing Rights RRS feed

  • Question

  • I need to write an SQL query that will list the projects a particular user is allowed to view based on their assigned Rights. A manager should be able to see all projects, a lower level user will see a subset.

    Is there a View that already exists that can do this? If not, which table(s) (and fields too if known!) should I be looking at?

    Thank you

    Tuesday, October 4, 2011 3:38 PM

Answers

All replies

  • I don't know of a SQL query to do this, but I do know that if you are using PS2010 and you can set yourself up as a delegate of the user, you can easily test what they can see.  I find I use this functionality all of the time when training users in the "joys" of security
    Ben Howard [MVP] blog | web
    Tuesday, October 4, 2011 7:30 PM
  • Hi there,

    It would not be supported to query the database directly with T-SQL as you would need to access the Published database to check this as the Published database is where the permissions are held. The only supported approach would be to use custom code using the PSI (http://msdn.microsoft.com/en-us/library/ee767707.aspx) or to use Ben's suggestion of delegation.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Tuesday, October 4, 2011 9:04 PM
  • Hi there,
    Agreed with Paul.
    "You should access the Draft, Published, and Archive databases only through the PSI. You can add data tables, fields (properties), and entities that are not defined in the Project Server 2010 database schema to the Reporting database. If you add tables to the core databases, you must also provide the full stack of a custom assembly, Web service, business objects, and data access. You can easily modify the Reporting database; we recommend that you do not modify the core Project Server databases.

     

    There is a report pack 2 of project server 2007 which has the EPM Management reports solution, Check if that is useful for you. Please downlaod & run the SQl query of "Security Permissions on User Level " report which pulls the data from Published database.
    http://archive.msdn.microsoft.com/EPMReportPack

     

    SQL Query:
    1. Change the DBToBeChecked variable & run it in your Project server published database. You  may need to do slight modification in SQL query


    /*
     EPM Governance - Security
      Query to display of users who have permissions set at the user level, which
      is contrary to best practices.

     

    */
    Declare @sqlquery1 nvarchar(max)
    Declare @DBToBeChecked nvarchar(100)
    -- Set DB variable to name of EPM Reporting DB that will be examined.
    Set @DBToBeChecked = N'ProjectServer2007_Litware_Published'

    set @sqlquery1 = N'use [' + @DBToBeChecked + N']

    SELECT
    u.res_name,
    c.CONV_STRING AS ''Permission Description'',
    p.WSEC_ALLOW AS ''Allowed'',
    p.WSEC_DENY AS ''Denied'',
    p.mod_date
    FROM dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS AS p
    INNER JOIN dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS AS r
    ON r.WSEC_REL_UID = p.WSEC_REL_UID
    INNER JOIN dbo.MSP_RESOURCES u
    ON r.WSEC_SP_GUID = u.RES_SECURITY_GUID
    INNER JOIN dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS AS f
    ON f.WSEC_FEA_ACT_UID = p.WSEC_FEA_ACT_UID
    INNER JOIN dbo.MSP_WEB_CONVERSIONS AS c
    ON f.WSEC_FEA_ACT_NAME_ID = c.CONV_VALUE
    where
    r.WSEC_CAT_UID IS NULL
    AND c.STRING_TYPE_UID = {guid ''946E9CC0-ED95-4F6F-877F-9A9ADAB57929''}
    AND f.WSEC_ON_OBJECT = 0
    AND f.WSEC_IS_ACTION <> 0
    order by res_name,c.CONV_STRING, mod_date 

    '
    exec (@sqlquery1)

     

     


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Wednesday, October 5, 2011 7:05 AM