Migrate-SPProjectDatabase : Error encountered while migrating project data in Content database. The INSERT permission was denied on the object 'MSP_RESOURCES', database 'PWA_Content', schema 'pjpub'.
I'm running a migration test from Project Server 2013 to Project Server 2016 and ran into a problem of my own making, so I wanted to share in the event this can help others.
My upgrade command is this:
Migrate-SPProjectDatabase -DatabaseName PWA_ProjectWebApp -SiteCollection "https://vm1/pwa"
"[A] Yes to All" got me this:
Migrate-SPProjectDatabase : Error encountered while migrating project data in Content database. The SELECT permission was denied on the object 'MSP_RESOURCES', database 'PWA_ProjectWebApp', schema 'pub'.
"[Y] Yes" got me this:
Migrate-SPProjectDatabase : Error encountered while migrating project data in Content database. The INSERT permission was denied on the object 'MSP_RESOURCES', database 'PWA_Content', schema 'pjpub'.
INSERT permission denied screams "this is a SQL Server permissions problem", so I opened SQL Server Management Studio.
I expanded the Security node of my SQL instance, then expanded Logins. I double-clicked my farmadmin account (which I was using to run the migrate command) and started looking at the database role memberships for my content and my PWA databases. I saw that in both databases, the account was in dbowner and also PSDataAccess, among others.
I decided to look at the PSDataAccess role by expanding the content database under the Databases node. I expanded the database itself and then Security > Roles > Database Roles and then I double-clicked on PSDataAccess. On the General page, I saw my farmadmin was a member. On the Securables page, things got interesting. In the top half of the Securables page, I scrolled down until I hit Schema pjpub and Name MSP_RESOURCES named in the error output and selected it. On the bottom half, I scrolled until I found the INSERT permission. This was set to Deny. I repeated these steps and confirmed the SELECT permission was also set to Deny in the PWA database on Schema Pub and Name MSP_RESOURCES. Aha!
I removed the farmadmin account from the PSDataAccess role and attempted my command again and the upgrade appears to be running fine.
PS C:\Users\farmadmin> Migrate-SPProjectDatabase -DatabaseName PWA_ProjectWebApp -SiteCollection "https://vm1/pwa"
Confirm
Are you sure you want to perform this action?
Performing the operation "Migrate-SPProjectDatabase" on target "PWA_ProjectWebApp".
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is "Y"): y
41.78% : PWA_ProjectWebApp.pub --> PWA_Content.pjpub
So, this leads me to wonder "why was my farmadmin account part of that database role?" because you can't run an upgrade with that role on the account.
I checked another instance of PWA, found the databases, and performed the same check. In those DBs, my farmadmin account was NOT part of that role.
Again, "aha!" I think I changed the SQL role membership for my farmadmin account doing some other testing (related to reporting) in the past on these databases and that came back to haunt me, because I never changed it back.
You can check out Brian Smith's blog for more about this role and its relation to reporting here: https://blogs.technet.microsoft.com/projectsupport/2016/08/17/project-server-2016-why-does-psdataccess-deny-select-on-tables/