Project Server 2010: After applying the March CU resources can’t be saved in PWA

*** Update 5/3/2017 – Please note – this issue was resolved with the June 2015 update – so install that or preferably a more recent one ***

Thanks to Adrian and Lars for raising this issue via the comments section of the March CU release blog.  You may also see this if you load the April 2015 or May 2015 CU – as we will not manage to get a fix into the May CU in time – but the correction of the issue is quite straightforward.

One symptom is that you edit a resource and go to save and you get an unknown error.

In the ULS logs you will see something like:

Exception occurred in method Microsoft.Office.Project.Server.BusinessLayer.Project.ProjectQueueUpdatePDPProjectCF System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘MSP_ReadLocalAndEnterpriseLookupTableInfoByUIDs’, database ‘ProjectServer_Published_PWA’, schema ‘dbo’.

This is due to the execute permission not being set on this brand new stored procedure that was introduced in the March CU to address tis issue:

You cannot update a local custom field by using Project Server Interface (PSI) if the field is associated with a lookup table. Additionally, you may receive a “GeneralUnhandledException” exception.

The stored procedure in question, as you can see from the exception above is MSP_ReadLocalAndEnterpriseLookupTableInfoByUIDs, and the most obvious failure is when saving a resource as it is used to handle the custom field info.  I suspect there are other failure modes too – possibly the issue with losing CF values that Aaron and Christoph reported in the same comment section.  I haven’t had a chance to confirm that and am not that familiar with that specific issue.

To be sure this is the issue you have – I would expect that looking at the Properties of the Stored Procedure, and the Permissions – you would see this:

image

When it should look like this:

image

To resolve the issue you can grant execute permissions to ProjectServerRole by executing the following command in the Project Server published (get your DBA to do this if this isn’t something you don’t have access or the knowledge to do)

GRANT EXECUTE ON dbo.MSP_ReadLocalAndEnterpriseLookupTableInfoByUIDs TO ProjectServerRole

GO

You should also correct the stored procedure – which should not actually contain the ‘GRANT’ command, but does so due to the fact that the ‘GO’ command was missing before the GRANT command in our database script that created the stored procedure.  To do this right click the stored procedure in SQL Server management Studio (under Programmability in the published DB) and select Script Stored Procedure as, Alter to. New Query Editor window.

image

In the stored procedure you can comment out the GRANT line by preceding it with – and then click execute.  You will find the line near the bottom.

image

The ironic thing is that depending on the permissions configuration it can get self corrected, as it will fail the first time for sure – but if the executing account has the right permission it will have then granted permissions from the GRANT command and the stored procedure would have executed just fine next time (this happened to me!).  If this happened to you too then it probably means that like me – you have a badly configured system with too few service accounts with too much power!

You will also want to ensure that you do the same for all your PWA instance databases – you could edit the addsps12.sql file that has the initial missing ‘GO’ – but best to leave this alone as if this file gets updated again it may skip it if the file does not look like the correct version.

Sorry for the inconvenience this issue has caused.