Environment: Project Server 2007, MS SQL Server 2008 R2
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