34 lines
1.3 KiB
Transact-SQL
34 lines
1.3 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[VW_ProjectAccessExtended] Script Date: 5/26/2016 9:28:33 AM ******/
|
|
if exists (select 1 from sys.views where name like 'VW_ProjectAccessExtended')
|
|
BEGIN
|
|
DROP VIEW [dbo].[VW_ProjectAccessExtended]
|
|
END
|
|
GO
|
|
|
|
/****** Object: View [dbo].[VW_ProjectAccessExtended] Script Date: 5/26/2016 9:28:33 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[VW_ProjectAccessExtended] AS
|
|
WITH UnitedProjectPermissions (UserId, ProjectId, [Read], [Write])
|
|
AS (select PA.UserId, PA.ProjectId, PA.[Read], PA.Write
|
|
from VW_ProjectAccess PA
|
|
WHERE (PA.[Read] = 1) OR (PA.Write = 1)
|
|
UNION
|
|
select distinct u.Id as UserId, project.Id as ProjectId, 1 as [Read], 1 as [Write]
|
|
from [AspNetUsers] u
|
|
inner join PeopleResource pr on pr.Email = u.Email
|
|
inner join PeopleResourceAllocation pra on pra.PeopleResourceId = pr.Id
|
|
inner join Scenario sc on sc.Id = pra.ScenarioId and sc.Status = 1
|
|
inner join Project part on part.Id = sc.ParentId
|
|
inner join Project project on project.Id = part.Id or project.Id = part.ParentProjectId)
|
|
select UserId, ProjectId, MAX([Read]) as [Read], MAX(Write) as Write -- USE Maximum permissions this way even if user set project access directly for project which assigned to this user then he will have full access
|
|
from UnitedProjectPermissions
|
|
group by UserId, ProjectId
|
|
GO |