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.

Comments (4)

  1. anonymouscommenter says:

    Thanks for following up on this and updating!

  2. anonymouscommenter says:

    We had the same issue, but we resolved it by just granting the service account that is running the service on the server "DBO" to the ProjectServer_Published. Would that not be correct based on your article?

  3. anonymouscommenter says:

    I too have this or a similiar issue. I can’t update the Users/Resources via Manage Users under server settings. However, checking the intial properties the Grant was set properly. I tried your fix anyhow and it still does not work. Thoughts?

    BTW, we only use a single service account for the entire farm and Taking backups and restoring to another instance of PWA the issue does not carryover. I can update the users.

  4. anonymouscommenter says:

    http://www.chanel-tote.co chanel tote
    http://www.louisvuittonartsy.us.com louis vuitton artsy mm
    http://www.louisvuittonmessengerbag.us Louis Vuitton Outlet Online Store Authentic
    http://www.longchamp-backpack.us longchamp backpack purse
    http://www.louisvuittonshoesmen.net.in louis vuitton shoes
    http://www.coachtotebags.us Coach Bags
    http://www.louisvuittonevaclutch.org Louis Vuitton Eva Clutch Bag
    http://www.guccisunglassesforwomen.co Gucci Sunglasses for Women on Sale
    http://www.vintage-chanel-bags.co vintage chanel bags
    http://www.chanel-diaper-bag.com chanel diaper bag
    http://www.longchamp-bags.us longchamp bags
    http://www.michaelkorsfactoryoutlet.biz michael kors factory outlet online
    http://www.mcm-handbags.us mcm handbags at bloomingdale’s
    http://www.prada-backpack.us prada backpack purse
    http://www.louisvuittonevaclutch.org louis vuitton eva clutch
    http://www.louisvuittonneverfullmm.co louis vuitton neverfull
    http://www.michaelkorshamiltontote.biz michael kors tote
    http://www.michaelkorsbagsoutlet.us.com michael kors outlet store
    http://www.louisvuittontravelbag.us louis vuitton travel bag
    http://www.chanel-bags-prices.co chanel bags prices fall winter 2015
    http://www.prada-crossbody-bag.us prada crossbody bags
    http://www.coachhandbagssale.co coach handbags sale outlet
    http://www.burberrystore.us Burberry Outlet Locations
    http://www.hermespurses.co Price of Hermes Purses
    http://www.longchamp-le-pliage-large.com longchamp le pliage large tote
    http://www.mcm-purse.us mcm purses handbags
    http://www.burberrybelts.us Gucci Belt
    http://www.chanelclassicflapbag.us chanel classic flap bag price
    http://www.louisvuittonmessengerbag.us louis vuitton messenger
    http://www.michaelkorsblackpurse.com michael kors black purse
    http://www.prada-loafers.us prada loafers
    http://www.prada-clutch.us Prada Clutches on Sale
    http://www.louisvuittondufflebag.com louis vuitton duffle bag
    http://www.louisvuittonhandbagsonsale.us.com cheap louis vuitton handbags
    http://www.burberrydiaperbag.us burberry diaper bag
    http://www.guccidiaperbag.us.com gucci diaper bag on sale
    http://www.michaelkorsbagsonsale.biz discount michael kors bags
    http://www.prada-tote.us.com prada tote
    http://www.cheapguccibelts.co cheap gucci belts
    http://www.burberrymenswallet.us burberry men’s wallet

Skip to main content