SPHA-Rule - SPDataAccess for ProjectQueueService16 in Content_Admin database SharePoint 2016

Question

We have the following issue with SharePoint 2016 environments where Project Servers is enabled.

SharePoint Health Analyzer is complaining about the SPHA-rule:

[Security] Database permissions are NOT correctly set. Identity 'DOMAIN\SharePointServiceAccount' of service 'ProjectQueueService16' does not have role 'SPDataAccess' in database 'Content_Admin' on server 'SQL2016'.
Identity 'DOMAIN\SharePointServiceAccount' of service 'ProjectQueueService16' does not have role 'SPDataAccess' in database 'Content_Admin' on server 'sql2016'.

I've checked the permissions a dozen of times, and even if I give the account db_owner on the Content_Admin database or sysadmin on the entire server the error doesnt' go away.

Thanks

Answer 1

I've managed to solve this isse by adding the service account manually to sql by using following command:

EXEC sp_addrolemember 'SPDataAccess', 'SPServiceAccount';  

To view the SPDataAccess Role membership on the database you can use the following query:

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.name = 'SPDataAccess'
ORDER BY DP1.name; 

This is the query the SPHA rules querys against the database, if everything works as expected it should return 1.

declare @p5 int
set @p5=0
exec sp_executesql N'select @is_member = COUNT(1) FROM
                                                    sys.database_role_members AS i 
                                                    JOIN sys.database_principals AS n ON i.role_principal_id = n.principal_id
                                                    JOIN sys.database_principals AS m ON i.member_principal_id = m.principal_id
                                                    WHERE n.name = @role AND m.Name = @user_name',N'@user_name nvarchar(128),@role nvarchar(128),@is_member int output',@user_name=N'SPServiceAccount',@role=N'SPDataAccess',@is_member=@p5 output
select @p5

Thanks,
Pelle

Answer 2

Hi @Pelle Ignatius ,

From your description, you have granted the service account of project server db_owner role, however the issue still exists?

Please note the SPDataAccess role replaced the db_owner role in SharePoint Server 2016, so make sure you grant the right role.

If the issue still exists, please check the following articles to configure Project Servers 2016:

https://learn.microsoft.com/en-us/project/install-and-configure-project-server-2016#to-register-a-managed-account

https://learn.microsoft.com/en-us/project/deploy-project-web-app

https://learn.microsoft.com/en-us/project/deploy-project-web-app-with-a-new-site-collection-project-server-2016

More information:

https://learn.microsoft.com/en-us/sharepoint/install/account-permissions-and-security-settings-in-sharepoint-server-2016#spdataaccess-database-role

---

If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.