Project Server 2016: Why does PSDataccess Deny Select on Tables?


In Project Server 2016 you can still make direct access to the database for reporting as long as each PWA site is in its own Content Database – but you may have noticed that the PSDataAccess role has an explicit Deny on Select for all tables in the database.  As an example lets take a look at pjrep.MSP_EpmEnterpriseProjectType – a useful table to find the different EPT information.  Looking at the PSDataAccess role permissions I can see the Deny is set.

image

This is not a bug or miss-configuration – but by design.  All access to table information is available through Table Valued Functions – or TVFs.  (Look under Programmability, Functions, Table Valued Functions if you are using SQL Server Management Studio).  These are basically functions that return a table as the result – and they are called with a a parameter of the SiteID of the PWA you are accessing.  The SiteID field can be found in each table usually as column 1 – and is the identifier used to ensure segregation of the multiple PWA sites that could exist in a single Content DB.  I say ‘could’ as we do support multiple PWA in the same database but we do NOT support direct database access to reporting if there is more than one (we don’t generate the views).  OLAP and ODATA is still supported in these situations *** (9/21/2017 correction - OLAP is NOT supported when there is more than one PWA) *** – and these methods also use the SiteID behind the scenes to ensure the correct data is returned.

The TVF to get to the pjrep.MSP_EpmEnterpriseProjectType table is called pjrep.MSP_TVF_EpmEnterpriseProjectType.  To retrieve data simple select and add the parameter – so for my site this happens to be

Select * from pjrep.MSP_TVF_EpmEnterpriseProjectType('8F2455DA-2745-40AE-B9E0-F1E6DD573AFB')

image

I’m aware this may break some of your reports written against earlier Project Server data stores – but hopefully the correction is pretty straightforward.


Comments (3)

  1. Christoph Mülder says:

    Hi Brian,
    of course the easiest way to handle this is just to use a user account that does not have the PSDataAccess role…. 🙂
    But it still makes things difficult. For example we have a webpart that turns a custom field on a PDP into a lookup field using values from the Project Server database. The data is accessed using the application pool account – and of course this has the PSDataAccessRole….. 🙁
    And we cannot write SQL statements easily joining several tables/views because we need to give every element the siteid parameter – either hardcode it into the statement or we need to put everything into stored procedures…..
    Of course all this is possible and can be amended for 2016 – but I cannot really understand the need for it. As you do not support SQL access if there is more than one site – why do you make it so complicated to get the data out of the system….?
    Regards
    Christoph

  2. Adrian says:

    Hi Brian, hi Christoph,

    we created a project custom field (free text), but we can only seem to find it in the project user view, but in none of the TVFs.
    Is it possible to access custom field via TFVs? If so, how?

    Note:
    select OBJECT_SCHEMA_NAME(object_id) as SchemaN, OBJECT_NAME(object_id) as objName, *
    from sys.columns
    –where name = ‘ProjectName’
    where name like ‘%binford%’
    This query showed only columns in the project user view, no results in TVFs at all.

    Note: We have the MSP_TVF_EpmProject, but nothing like a “MSP_TVF_EpmProject_UserView” or similar.
    Should that view exist, but our instance is broken?

    Thank you and kind regards,
    Adrian

    1. Christoph Mülder says:

      Hi Adrian,
      also no TFVs for the UserViews in my environment. I suggest you continue using the view and use a user for accessing the data that has db_datareader or dbo, but no PSDataAccess.
      Regards
      Christoph

Skip to main content