Report Requirement: User Permissions across Projects in Project Server

Client Requirement: Recently, we have received the new Report requirement on User permissions across projects. However, we didn’t find any out of the box report so we have created the custom report. Report requirement mock report is pasted below:

Note: User Permission against said Project we have categorized as per below logic:

• Project managers who have published a project or who have Save Project permissions on a project are added to the Project Managers (Microsoft Office Project Server) site group.
• Team members with assignments in a project are added to the Team members (Microsoft Office Project Server) site group.
• Other Project Server users with View Project Workspace permission on a project are added to the Readers (Microsoft Office Project Server) site group.

Solution:  To achieve above report we have fetched data from the following databases –

1. ProjectServer_Reporting Datbase for resources those are assigned in projects and owner of projects.

2. ProjectServer_PublishedDatabase for resources those are part of projects but haven’t assigned to any task in the Project.

Please note in below query we have used the Union all operator to combine the result-set of two SELECT statement to add the project resources data those are not assigned to any activity in the projects.

SQL Query Developed:

SELECT  DISTINCT PRR.ResourceName as UserName, PRR.ResourceNTAccount as UserFullName, case when PRR.ResourceIsActive=1 then ‘Active’ else ‘Inactive’ end as ResourceStatus, P.ProjectName, case when PRR.ResourceUID=p.ProjectOwnerResourceUID then ‘ProjectManager’ when A.ResourceUID is not null then ‘Team Member’ else ‘Reader’ end as ProjectPermission,
p.ProjectWorkspaceInternalHRef as ProjectUrl FROM dbo.MSP_EpmProject_UserView P
INNER JOIN (Select R.ResourceName,R.ResourceNTAccount,R.ResourceIsActive,R.ResourceUID,PR.Proj_UID
from Badal_Published.dbo.MSP_PROJECT_RESOURCES PR
INNER JOIN dbo.MSP_EpmResource_UserView R ON PR.RES_UID = R.RESOURCEUID  UNION ALL
Select R.ResourceName,R.ResourceNTAccount,R.ResourceIsActive,R.ResourceUID,PR.ProjectUID as Proj_UID from DBO.MSP_EpmProject_UserView PR
INNER JOIN dbo.MSP_EpmResource_UserView R ON PR.ProjectOwnerResourceUID = R.RESOURCEUID)as PRR on p.ProjectUID=PRR.Proj_UID
LEFT OUTER JOIN dbo.MSP_EpmAssignment_UserView A ON P.PROJECTUID = A.PROJECTUID and a.ResourceUID=PRR.ResourceUID
where ResourceNTAccount is not NUll

Hope this helps you.

Note: Direct access to the Published DB is not supported so please try at your own risk.

Cheers,

Badal