--**************************************************************************************************
--*
--*	This script will create the SQL Server security views needed for Microsoft Project Server
--*
--*	Privileges needed: member of db_owner or db_ddladmin
--*
--**************************************************************************************************

--**************************************************************************************************
--*		MSP_ASSIGNMENT_BASELINES_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSIGNMENT_BASELINES_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSIGNMENT_BASELINES_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_ASSIGNMENT_BASELINES_PROJ_READVIEW
AS
SELECT *
FROM MSP_ASSIGNMENT_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_ASSIGNMENT_BASELINES_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENT_BASELINES_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSIGNMENT_BASELINES_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSIGNMENT_BASELINES_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_ASSIGNMENT_BASELINES_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_ASSIGNMENT_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_ASSIGNMENT_BASELINES_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENTS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSIGNMENTS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSIGNMENTS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_ASSIGNMENTS_PROJ_READVIEW
AS
SELECT *
FROM MSP_ASSIGNMENTS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_ASSIGNMENTS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENTS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSIGNMENTS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSIGNMENTS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_ASSIGNMENTS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_ASSIGNMENTS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_ASSIGNMENTS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSN_ENTERPRISE_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSN_ENTERPRISE_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSN_ENTERPRISE_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_ASSN_ENTERPRISE_PROJ_READVIEW
AS
SELECT *
FROM MSP_ASSN_ENTERPRISE
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_ASSN_ENTERPRISE_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSN_ENTERPRISE_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSN_ENTERPRISE_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSN_ENTERPRISE_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_ASSN_ENTERPRISE_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_ASSN_ENTERPRISE
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_ASSN_ENTERPRISE_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ATTRIBUTE_STRINGS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ATTRIBUTE_STRINGS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ATTRIBUTE_STRINGS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_ATTRIBUTE_STRINGS_PROJ_READVIEW
AS
SELECT *
FROM MSP_ATTRIBUTE_STRINGS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_ATTRIBUTE_STRINGS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ATTRIBUTE_STRINGS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ATTRIBUTE_STRINGS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ATTRIBUTE_STRINGS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_ATTRIBUTE_STRINGS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_ATTRIBUTE_STRINGS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_ATTRIBUTE_STRINGS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_AVAILABILITY_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_AVAILABILITY_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_AVAILABILITY_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_AVAILABILITY_PROJ_READVIEW
AS
SELECT *
FROM MSP_AVAILABILITY
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_AVAILABILITY_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_AVAILABILITY_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_AVAILABILITY_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_AVAILABILITY_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_AVAILABILITY_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_AVAILABILITY
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_AVAILABILITY_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CALENDAR_DATA_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CALENDAR_DATA_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CALENDAR_DATA_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_CALENDAR_DATA_PROJ_READVIEW
AS
SELECT *
FROM MSP_CALENDAR_DATA
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_CALENDAR_DATA_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CALENDAR_DATA_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CALENDAR_DATA_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CALENDAR_DATA_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_CALENDAR_DATA_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_CALENDAR_DATA
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_CALENDAR_DATA_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CALENDARS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CALENDARS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CALENDARS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_CALENDARS_PROJ_READVIEW
AS
SELECT *
FROM MSP_CALENDARS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
       OR (CAL_UID<0)
       OR (CAL_IS_BASE_CAL=1)
       OR (RES_UID=0)
       OR (CAL_UID IN (SELECT SEC_CAL_UID FROM MSP_RESGLOBAL_SECURITY))
       OR (CAL_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_CALENDARS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CALENDARS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CALENDARS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CALENDARS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_CALENDARS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_CALENDARS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR (CAL_UID<0) 
       OR (CAL_IS_BASE_CAL=1)
       OR (CAL_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_CALENDARS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CODE_FIELDS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CODE_FIELDS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CODE_FIELDS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_CODE_FIELDS_PROJ_READVIEW
AS
SELECT *
FROM MSP_CODE_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_CODE_FIELDS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CODE_FIELDS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CODE_FIELDS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CODE_FIELDS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_CODE_FIELDS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_CODE_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (CODE_CATEGORY=1 AND CODE_REF_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY))
       OR
       (CODE_CATEGORY=2 AND CODE_REF_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_CODE_FIELDS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CONVERSIONS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CONVERSIONS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CONVERSIONS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_CONVERSIONS_PROJ_READVIEW
AS
SELECT *
FROM MSP_CONVERSIONS
GO


GRANT  SELECT  ON [dbo].[MSP_CONVERSIONS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CONVERSIONS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CONVERSIONS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CONVERSIONS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_CONVERSIONS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_CONVERSIONS
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_CONVERSIONS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_DATE_FIELDS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_DATE_FIELDS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_DATE_FIELDS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_DATE_FIELDS_PROJ_READVIEW
AS
SELECT *
FROM MSP_DATE_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_DATE_FIELDS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_DATE_FIELDS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_DATE_FIELDS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_DATE_FIELDS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_DATE_FIELDS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_DATE_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (DATE_CATEGORY=1 AND DATE_REF_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY))
       OR
       (DATE_CATEGORY=2 AND DATE_REF_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_DATE_FIELDS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_DURATION_FIELDS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_DURATION_FIELDS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_DURATION_FIELDS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_DURATION_FIELDS_PROJ_READVIEW
AS
SELECT *
FROM MSP_DURATION_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_DURATION_FIELDS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_DURATION_FIELDS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_DURATION_FIELDS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_DURATION_FIELDS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_DURATION_FIELDS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_DURATION_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (DUR_CATEGORY=1 AND DUR_REF_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY))
       OR
       (DUR_CATEGORY=2 AND DUR_REF_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_DURATION_FIELDS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_FIELD_ATTRIBUTES_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_FIELD_ATTRIBUTES_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_FIELD_ATTRIBUTES_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_FIELD_ATTRIBUTES_PROJ_READVIEW
AS
SELECT *
FROM MSP_FIELD_ATTRIBUTES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_FIELD_ATTRIBUTES_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_FIELD_ATTRIBUTES_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_FIELD_ATTRIBUTES_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_FIELD_ATTRIBUTES_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_FIELD_ATTRIBUTES_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_FIELD_ATTRIBUTES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_FIELD_ATTRIBUTES_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_FLAG_FIELDS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_FLAG_FIELDS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_FLAG_FIELDS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_FLAG_FIELDS_PROJ_READVIEW
AS
SELECT *
FROM MSP_FLAG_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_FLAG_FIELDS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_FLAG_FIELDS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_FLAG_FIELDS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_FLAG_FIELDS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_FLAG_FIELDS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_FLAG_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (FLAG_CATEGORY=1 AND FLAG_REF_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY))
       OR
       (FLAG_CATEGORY=2 AND FLAG_REF_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_FLAG_FIELDS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_LINKS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_LINKS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_LINKS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_LINKS_PROJ_READVIEW
AS
SELECT *
FROM MSP_LINKS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_LINKS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_LINKS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_LINKS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_LINKS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_LINKS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_LINKS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_LINKS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_NUMBER_FIELDS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_NUMBER_FIELDS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_NUMBER_FIELDS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_NUMBER_FIELDS_PROJ_READVIEW
AS
SELECT *
FROM MSP_NUMBER_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_NUMBER_FIELDS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_NUMBER_FIELDS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_NUMBER_FIELDS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_NUMBER_FIELDS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_NUMBER_FIELDS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_NUMBER_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (NUM_CATEGORY=1 AND NUM_REF_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY))
       OR
       (NUM_CATEGORY=2 AND NUM_REF_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_NUMBER_FIELDS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_OUTLINE_CODES_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_OUTLINE_CODES_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_OUTLINE_CODES_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_OUTLINE_CODES_PROJ_READVIEW
AS
SELECT *
FROM MSP_OUTLINE_CODES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_OUTLINE_CODES_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_OUTLINE_CODES_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_OUTLINE_CODES_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_OUTLINE_CODES_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_OUTLINE_CODES_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_OUTLINE_CODES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_OUTLINE_CODES_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_PROJECTS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_PROJECTS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_PROJECTS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_PROJECTS_PROJ_READVIEW
AS
SELECT *
FROM MSP_PROJECTS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_PROJECTS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_PROJECTS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_PROJECTS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_PROJECTS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_PROJECTS_PROJ_WRITEVIEW
AS
SELECT
PROJ_ID, PROJ_NAME, PROJ_PROP_AUTHOR, PROJ_OPT_CALC_ACT_COSTS, PROJ_PROP_CATEGORY,
PROJ_PROP_COMPANY, PROJ_OPT_CRITICAL_SLACK_LIMIT, PROJ_OPT_CURRENCY_DIGITS, 
PROJ_OPT_CURRENCY_POSITION, PROJ_OPT_CURRENCY_SYMBOL, PROJ_OPT_NEW_ARE_EFFORT_DRIVEN, 
PROJ_INFO_CURRENT_DATE, PROJ_OPT_DEF_FINISH_TIME, PROJ_OPT_DEF_FIX_COST_ACCRUAL, 
PROJ_OPT_MINUTES_PER_DAY, PROJ_OPT_MINUTES_PER_WEEK, PROJ_OPT_DEF_OVT_RATE, 
PROJ_OPT_DEF_STD_RATE, PROJ_OPT_DEF_START_TIME, PROJ_OPT_DEF_TASK_TYPE, 
PROJ_OPT_DUR_ENTRY_FMT, PROJ_INFO_FINISH_DATE, PROJ_OPT_HONOR_CONSTRAINTS, 
PROJ_PROP_KEYWORDS, PROJ_LAST_SAVED, PROJ_PROP_MANAGER, PROJ_OPT_MULT_CRITICAL_PATHS, 
PROJ_CREATION_DATE, PROJ_POOL_ATTACHED_TO, PROJ_INFO_CAL_NAME, PROJ_IS_RES_POOL, 
PROJ_INFO_SCHED_FROM, PROJ_OPT_SPLIT_IN_PROGRESS, PROJ_OPT_SPREAD_ACT_COSTS, 
PROJ_OPT_SPREAD_PCT_COMP, PROJ_INFO_START_DATE, PROJ_INFO_STATUS_DATE, 
PROJ_PROP_SUBJECT, PROJ_PROP_TITLE, PROJ_OPT_TASK_UPDATES_RES, PROJ_OPT_WORK_ENTRY_FMT, 
PROJ_OPT_CALC_SUB_AS_SUMMARY, PROJ_OPT_WEEK_START_DAY, PROJ_OPT_FY_START_MONTH, 
PROJ_OPT_FY_USE_START_YR, PROJ_OPT_DAYS_PER_MONTH, PROJ_OPT_NEW_TASK_EST, 
PROJ_OPT_SHOW_EST_DUR, PROJ_OPT_EXPAND_TIMEPHASED, PROJ_EXT_EDITED_DATE, 
PROJ_EXT_EDITED_DUR, PROJ_EXT_EDITED_NUM, PROJ_EXT_EDITED_FLAG, PROJ_EXT_EDITED_CODE, 
PROJ_EXT_EDITED_TEXT, PROJ_EXT_EDITED, PROJ_DATA_SOURCE, PROJ_READ_ONLY, 
PROJ_READ_WRITE, PROJ_READ_COUNT, PROJ_LOCKED, PROJ_MACHINE_ID, RESERVED_BINARY_DATA, 
PROJ_CHECKEDOUT, PROJ_CHECKOUTBY, PROJ_CHECKOUTDATE, PROJ_PROJECT, 
PROJ_VERSION, PROJ_CREATION_DATE_EX, PROJ_ACTUALS_SYNCH, PROJ_ADMINPROJECT, 
PROJ_ENT_LIST_SEPARATOR, PROJ_IGNORE_FRONT_END
FROM MSP_PROJECTS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_PROJECTS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RES_GLOBAL_BINARY_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RES_GLOBAL_BINARY_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RES_GLOBAL_BINARY_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_RES_GLOBAL_BINARY_PROJ_READVIEW
AS
SELECT *
FROM MSP_RES_GLOBAL_BINARY
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
       OR
       (GLB_CATEGORY=1 AND GLB_REF_UID IN (SELECT SEC_RES_UID FROM MSP_RESGLOBAL_SECURITY))
       OR
       (GLB_CATEGORY=2))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_RES_GLOBAL_BINARY_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RES_GLOBAL_BINARY_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RES_GLOBAL_BINARY_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RES_GLOBAL_BINARY_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_RES_GLOBAL_BINARY_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_RES_GLOBAL_BINARY
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (GLB_CATEGORY=1 AND GLB_REF_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY))
       OR
       (GLB_CATEGORY=2))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_RES_GLOBAL_BINARY_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_BASELINES_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCE_BASELINES_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCE_BASELINES_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCE_BASELINES_PROJ_READVIEW
AS
SELECT *
FROM MSP_RESOURCE_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_RESOURCE_BASELINES_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_BASELINES_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCE_BASELINES_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCE_BASELINES_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCE_BASELINES_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_RESOURCE_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
         OR
       (RES_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_RESOURCE_BASELINES_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_RATES_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCE_RATES_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCE_RATES_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCE_RATES_PROJ_READVIEW
AS
SELECT *
FROM MSP_RESOURCE_RATES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_RESOURCE_RATES_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_RATES_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCE_RATES_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCE_RATES_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCE_RATES_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_RESOURCE_RATES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
         OR
       (RES_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_RESOURCE_RATES_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCES_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCES_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCES_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCES_PROJ_READVIEW
AS
SELECT *
FROM MSP_RESOURCES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
       OR (RES_UID <= 0)
       OR (RES_UID IN (SELECT SEC_RES_UID FROM MSP_RESGLOBAL_SECURITY))
       OR (RES_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_RESOURCES_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCES_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCES_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCES_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCES_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_RESOURCES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (RES_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_RESOURCES_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_STRING_TYPES_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_STRING_TYPES_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_STRING_TYPES_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_STRING_TYPES_PROJ_READVIEW
AS
SELECT *
FROM MSP_STRING_TYPES
GO


GRANT  SELECT  ON [dbo].[MSP_STRING_TYPES_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_STRING_TYPES_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_STRING_TYPES_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_STRING_TYPES_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_STRING_TYPES_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_STRING_TYPES
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_STRING_TYPES_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TASK_BASELINES_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TASK_BASELINES_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TASK_BASELINES_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_TASK_BASELINES_PROJ_READVIEW
AS
SELECT *
FROM MSP_TASK_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_TASK_BASELINES_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TASK_BASELINES_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TASK_BASELINES_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TASK_BASELINES_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_TASK_BASELINES_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_TASK_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_TASK_BASELINES_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TASKS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TASKS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TASKS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_TASKS_PROJ_READVIEW
AS
SELECT *
FROM MSP_TASKS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_TASKS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TASKS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TASKS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TASKS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_TASKS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_TASKS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_TASKS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TEXT_FIELDS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TEXT_FIELDS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TEXT_FIELDS_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_TEXT_FIELDS_PROJ_READVIEW
AS
SELECT *
FROM MSP_TEXT_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_TEXT_FIELDS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TEXT_FIELDS_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TEXT_FIELDS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TEXT_FIELDS_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_TEXT_FIELDS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_TEXT_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (TEXT_CATEGORY=1 AND TEXT_REF_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY))
       OR
       (TEXT_CATEGORY=2 AND TEXT_REF_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_TEXT_FIELDS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TIMEPHASED_DATA_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TIMEPHASED_DATA_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TIMEPHASED_DATA_PROJ_READVIEW]
GO


CREATE VIEW dbo.MSP_TIMEPHASED_DATA_PROJ_READVIEW
AS
SELECT *
FROM MSP_TIMEPHASED_DATA
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_TIMEPHASED_DATA_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TIMEPHASED_DATA_PROJ_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TIMEPHASED_DATA_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TIMEPHASED_DATA_PROJ_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_TIMEPHASED_DATA_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_TIMEPHASED_DATA
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_TIMEPHASED_DATA_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENTS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSIGNMENTS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSIGNMENTS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_ASSIGNMENTS_RES_READVIEW
AS
SELECT *
FROM MSP_ASSIGNMENTS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3) AND
RES_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_ASSIGNMENTS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENTS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSIGNMENTS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSIGNMENTS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_ASSIGNMENTS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_ASSIGNMENTS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3) AND
RES_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_ASSIGNMENTS_RES_WRITEVIEW]  TO [MSProjectRole]
GO


--**************************************************************************************************
--*		MSP_ASSN_ENTERPRISE_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSN_ENTERPRISE_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSN_ENTERPRISE_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_ASSN_ENTERPRISE_RES_READVIEW
AS
SELECT *
FROM MSP_ASSN_ENTERPRISE
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS  WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3) AND
(ASSN_ENTSUMPROJID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0) OR
 RES_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0) OR
 RES_UID is NULL)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_ASSN_ENTERPRISE_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSN_ENTERPRISE_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSN_ENTERPRISE_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSN_ENTERPRISE_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_ASSN_ENTERPRISE_RES_WRITEVIEW
AS
SELECT *
FROM MSP_ASSN_ENTERPRISE
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)  WHERE PROJ_TYPE = 3) AND
ASSN_ENTSUMPROJID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_ASSN_ENTERPRISE_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENT_BASELINES_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSIGNMENT_BASELINES_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSIGNMENT_BASELINES_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_ASSIGNMENT_BASELINES_RES_READVIEW
AS
SELECT *
FROM MSP_ASSIGNMENT_BASELINES 
WHERE 
PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)  WHERE  PROJ_TYPE = 3) AND
ASSN_UID IN (select ASSN_UID from MSP_ASSN_ENTERPRISE_RES_READVIEW)AND
AB_REF_ENT_PROJ_ID IN (select ASSN_ENTSUMPROJID from MSP_ASSN_ENTERPRISE_RES_READVIEW)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_ASSIGNMENT_BASELINES_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENT_BASELINES_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ASSIGNMENT_BASELINES_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ASSIGNMENT_BASELINES_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_ASSIGNMENT_BASELINES_RES_WRITEVIEW
AS
SELECT *
FROM MSP_ASSIGNMENT_BASELINES 
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE  PROJ_TYPE = 3) AND
AB_REF_ENT_PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_ASSIGNMENT_BASELINES_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ATTRIBUTE_STRINGS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ATTRIBUTE_STRINGS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ATTRIBUTE_STRINGS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_ATTRIBUTE_STRINGS_RES_READVIEW
AS
SELECT *
FROM MSP_ATTRIBUTE_STRINGS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_ATTRIBUTE_STRINGS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_ATTRIBUTE_STRINGS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_ATTRIBUTE_STRINGS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_ATTRIBUTE_STRINGS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_ATTRIBUTE_STRINGS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_ATTRIBUTE_STRINGS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_ATTRIBUTE_STRINGS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_AVAILABILITY_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_AVAILABILITY_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_AVAILABILITY_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_AVAILABILITY_RES_READVIEW
AS
SELECT *
FROM MSP_AVAILABILITY
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3) AND
RES_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_AVAILABILITY_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_AVAILABILITY_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_AVAILABILITY_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_AVAILABILITY_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_AVAILABILITY_RES_WRITEVIEW
AS
SELECT *
FROM MSP_AVAILABILITY
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)  WHERE PROJ_TYPE = 3) AND
RES_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_AVAILABILITY_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CALENDAR_DATA_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CALENDAR_DATA_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CALENDAR_DATA_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_CALENDAR_DATA_RES_READVIEW
AS
SELECT *
FROM MSP_CALENDAR_DATA
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)  WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_CALENDAR_DATA_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CALENDAR_DATA_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CALENDAR_DATA_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CALENDAR_DATA_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_CALENDAR_DATA_RES_WRITEVIEW
AS
SELECT *
FROM MSP_CALENDAR_DATA
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_CALENDAR_DATA_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CALENDARS_RES_READVIEW
--**************************************************************************************************

IF OBJECTPROPERTY(OBJECT_ID('dbo.MSP_CALENDARS_RES_READVIEW'), 'IsView') = 1
DROP VIEW dbo.MSP_CALENDARS_RES_READVIEW
GO
 

CREATE VIEW dbo.MSP_CALENDARS_RES_READVIEW
AS
SELECT ca.*
FROM MSP_CALENDARS ca
JOIN MSP_PROJECTS p WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) ON (ca.PROJ_ID = p.PROJ_ID AND p.PROJ_TYPE = 3)
WHERE 
  RES_UID IS NULL 
  OR RES_UID in (-1,0) 
  OR RES_UID IN (SELECT a.ERUID 
                 FROM MSP_RES_SECURITY a  
                 WHERE a.SEC_READCOUNT > 0
                 AND @@SPID = a.SEC_SPID)
  OR RES_UID IN (SELECT b.RES_UID 
                 FROM dbo.MSP_ASSN_ENTERPRISE b 
                 JOIN dbo.MSP_PROJ_SECURITY c ON (b.ASSN_ENTSUMPROJID = c.PROJ_ID AND @@SPID = c.SEC_SPID AND c.SEC_READCOUNT > 0)) 
  OR RES_UID IN (SELECT d.RES_EUID 
                 FROM dbo.MSP_RESOURCES d 
                 JOIN dbo.MSP_PROJ_SECURITY e ON (d.PROJ_ID = e.PROJ_ID AND @@SPID = e.SEC_SPID AND e.SEC_READCOUNT > 0)
                 WHERE d.RES_EUID > 0)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_CALENDARS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CALENDARS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CALENDARS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CALENDARS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_CALENDARS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_CALENDARS WHERE
(PROJ_ID in (select PROJ_ID from MSP_PROJECTS p WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) where p.PROJ_TYPE = 3))
AND
(RES_UID IS NULL OR RES_UID IN (0,-1) OR RES_UID in (select x.ERUID from MSP_RES_SECURITY x where 
(SEC_WRITECOUNT > 0 AND @@SPID = x.SEC_SPID)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_CALENDARS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CODE_FIELDS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CODE_FIELDS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CODE_FIELDS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_CODE_FIELDS_RES_READVIEW
AS
SELECT *
FROM MSP_CODE_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_CODE_FIELDS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CODE_FIELDS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CODE_FIELDS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CODE_FIELDS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_CODE_FIELDS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_CODE_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_CODE_FIELDS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CONVERSIONS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CONVERSIONS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CONVERSIONS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_CONVERSIONS_RES_READVIEW
AS
SELECT *
FROM MSP_CONVERSIONS
GO


GRANT  SELECT  ON [dbo].[MSP_CONVERSIONS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_CONVERSIONS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_CONVERSIONS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_CONVERSIONS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_CONVERSIONS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_CONVERSIONS
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_CONVERSIONS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_DATE_FIELDS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_DATE_FIELDS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_DATE_FIELDS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_DATE_FIELDS_RES_READVIEW
AS
SELECT *
FROM MSP_DATE_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_DATE_FIELDS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_DATE_FIELDS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_DATE_FIELDS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_DATE_FIELDS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_DATE_FIELDS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_DATE_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_DATE_FIELDS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_DURATION_FIELDS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_DURATION_FIELDS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_DURATION_FIELDS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_DURATION_FIELDS_RES_READVIEW
AS
SELECT *
FROM MSP_DURATION_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)  WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_DURATION_FIELDS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_DURATION_FIELDS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_DURATION_FIELDS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_DURATION_FIELDS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_DURATION_FIELDS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_DURATION_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_DURATION_FIELDS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_FIELD_ATTRIBUTES_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_FIELD_ATTRIBUTES_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_FIELD_ATTRIBUTES_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_FIELD_ATTRIBUTES_RES_READVIEW
AS
SELECT *
FROM MSP_FIELD_ATTRIBUTES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_FIELD_ATTRIBUTES_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_FIELD_ATTRIBUTES_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_FIELD_ATTRIBUTES_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_FIELD_ATTRIBUTES_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_FIELD_ATTRIBUTES_RES_WRITEVIEW
AS
SELECT *
FROM MSP_FIELD_ATTRIBUTES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_FIELD_ATTRIBUTES_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_FLAG_FIELDS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_FLAG_FIELDS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_FLAG_FIELDS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_FLAG_FIELDS_RES_READVIEW
AS
SELECT *
FROM MSP_FLAG_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_FLAG_FIELDS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_FLAG_FIELDS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_FLAG_FIELDS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_FLAG_FIELDS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_FLAG_FIELDS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_FLAG_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_FLAG_FIELDS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_LINKS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_LINKS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_LINKS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_LINKS_RES_READVIEW
AS
SELECT *
FROM MSP_LINKS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_LINKS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_LINKS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_LINKS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_LINKS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_LINKS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_LINKS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_LINKS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_NUMBER_FIELDS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_NUMBER_FIELDS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_NUMBER_FIELDS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_NUMBER_FIELDS_RES_READVIEW
AS
SELECT *
FROM MSP_NUMBER_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_NUMBER_FIELDS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_NUMBER_FIELDS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_NUMBER_FIELDS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_NUMBER_FIELDS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_NUMBER_FIELDS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_NUMBER_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_NUMBER_FIELDS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_OUTLINE_CODES_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_OUTLINE_CODES_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_OUTLINE_CODES_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_OUTLINE_CODES_RES_READVIEW
AS
SELECT *
FROM MSP_OUTLINE_CODES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_OUTLINE_CODES_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_OUTLINE_CODES_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_OUTLINE_CODES_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_OUTLINE_CODES_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_OUTLINE_CODES_RES_WRITEVIEW
AS
SELECT *
FROM MSP_OUTLINE_CODES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_OUTLINE_CODES_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_PROJECTS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_PROJECTS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_PROJECTS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_PROJECTS_RES_READVIEW
AS
SELECT *
FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) 
WHERE PROJ_TYPE = 3
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_PROJECTS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_PROJECTS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_PROJECTS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_PROJECTS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_PROJECTS_RES_WRITEVIEW
AS
SELECT
PROJ_ID, PROJ_NAME, PROJ_PROP_AUTHOR, PROJ_OPT_CALC_ACT_COSTS, PROJ_PROP_CATEGORY,
PROJ_PROP_COMPANY, PROJ_OPT_CRITICAL_SLACK_LIMIT, PROJ_OPT_CURRENCY_DIGITS, 
PROJ_OPT_CURRENCY_POSITION, PROJ_OPT_CURRENCY_SYMBOL, PROJ_OPT_NEW_ARE_EFFORT_DRIVEN, 
PROJ_INFO_CURRENT_DATE, PROJ_OPT_DEF_FINISH_TIME, PROJ_OPT_DEF_FIX_COST_ACCRUAL, 
PROJ_OPT_MINUTES_PER_DAY, PROJ_OPT_MINUTES_PER_WEEK, PROJ_OPT_DEF_OVT_RATE, 
PROJ_OPT_DEF_STD_RATE, PROJ_OPT_DEF_START_TIME, PROJ_OPT_DEF_TASK_TYPE, 
PROJ_OPT_DUR_ENTRY_FMT, PROJ_INFO_FINISH_DATE, PROJ_OPT_HONOR_CONSTRAINTS, 
PROJ_PROP_KEYWORDS, PROJ_LAST_SAVED, PROJ_PROP_MANAGER, PROJ_OPT_MULT_CRITICAL_PATHS, 
PROJ_CREATION_DATE, PROJ_POOL_ATTACHED_TO, PROJ_INFO_CAL_NAME, PROJ_IS_RES_POOL, 
PROJ_INFO_SCHED_FROM, PROJ_OPT_SPLIT_IN_PROGRESS, PROJ_OPT_SPREAD_ACT_COSTS, 
PROJ_OPT_SPREAD_PCT_COMP, PROJ_INFO_START_DATE, PROJ_INFO_STATUS_DATE, 
PROJ_PROP_SUBJECT, PROJ_PROP_TITLE, PROJ_OPT_TASK_UPDATES_RES, PROJ_OPT_WORK_ENTRY_FMT, 
PROJ_OPT_CALC_SUB_AS_SUMMARY, PROJ_OPT_WEEK_START_DAY, PROJ_OPT_FY_START_MONTH, 
PROJ_OPT_FY_USE_START_YR, PROJ_OPT_DAYS_PER_MONTH, PROJ_OPT_NEW_TASK_EST, 
PROJ_OPT_SHOW_EST_DUR, PROJ_OPT_EXPAND_TIMEPHASED, PROJ_EXT_EDITED_DATE, 
PROJ_EXT_EDITED_DUR, PROJ_EXT_EDITED_NUM, PROJ_EXT_EDITED_FLAG, PROJ_EXT_EDITED_CODE, 
PROJ_EXT_EDITED_TEXT, PROJ_EXT_EDITED, PROJ_DATA_SOURCE, PROJ_READ_ONLY, 
PROJ_READ_WRITE, PROJ_READ_COUNT, PROJ_LOCKED, PROJ_MACHINE_ID, RESERVED_BINARY_DATA, 
PROJ_CHECKEDOUT, PROJ_CHECKOUTBY, PROJ_CHECKOUTDATE, PROJ_PROJECT, 
PROJ_VERSION, PROJ_CREATION_DATE_EX, PROJ_ACTUALS_SYNCH, PROJ_ADMINPROJECT, 
PROJ_ENT_LIST_SEPARATOR, PROJ_IGNORE_FRONT_END
FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)
WHERE PROJ_TYPE = 3
WITH CHECK OPTION
GO


GRANT  SELECT, UPDATE ON [dbo].[MSP_PROJECTS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RES_GLOBAL_BINARY_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RES_GLOBAL_BINARY_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RES_GLOBAL_BINARY_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_RES_GLOBAL_BINARY_RES_READVIEW
AS
SELECT *
FROM MSP_RES_GLOBAL_BINARY
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_RES_GLOBAL_BINARY_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RES_GLOBAL_BINARY_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RES_GLOBAL_BINARY_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RES_GLOBAL_BINARY_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_RES_GLOBAL_BINARY_RES_WRITEVIEW
AS
SELECT *
FROM MSP_RES_GLOBAL_BINARY
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_RES_GLOBAL_BINARY_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_BASELINES_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCE_BASELINES_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCE_BASELINES_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCE_BASELINES_RES_READVIEW
AS
SELECT *
FROM MSP_RESOURCE_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)  WHERE  PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_RESOURCE_BASELINES_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_BASELINES_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCE_BASELINES_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCE_BASELINES_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCE_BASELINES_RES_WRITEVIEW
AS
SELECT *
FROM MSP_RESOURCE_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_RESOURCE_BASELINES_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_RATES_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCE_RATES_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCE_RATES_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCE_RATES_RES_READVIEW
AS
SELECT *
FROM MSP_RESOURCE_RATES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)  WHERE PROJ_TYPE = 3) AND
RES_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_RESOURCE_RATES_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_RATES_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCE_RATES_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCE_RATES_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCE_RATES_RES_WRITEVIEW
AS
SELECT *
FROM MSP_RESOURCE_RATES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS  WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3) AND
RES_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_RESOURCE_RATES_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCES_RES_READVIEW
--**************************************************************************************************

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MSP_RESOURCES_RES_READVIEW]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [dbo].[MSP_RESOURCES_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCES_RES_READVIEW
AS
SELECT
  r.*
FROM
  dbo.MSP_RESOURCES r
JOIN dbo.MSP_PROJECTS p WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) ON (r.PROJ_ID = p.PROJ_ID AND PROJ_TYPE = 3)
WHERE  
   RES_UID <= 0
   OR RES_UID IN (SELECT a.ERUID 
                  FROM dbo.MSP_RES_SECURITY a 
                  WHERE @@SPID = a.SEC_SPID 
                  AND a.SEC_READCOUNT > 0) 
   OR RES_UID IN (SELECT b.RES_UID 
                  FROM dbo.MSP_ASSN_ENTERPRISE b 
                  JOIN dbo.MSP_PROJ_SECURITY c ON (b.ASSN_ENTSUMPROJID = c.PROJ_ID AND @@SPID = c.SEC_SPID AND c.SEC_READCOUNT > 0)) 
   OR RES_UID IN (SELECT d.RES_EUID 
                  FROM dbo.MSP_RESOURCES d 
                  JOIN dbo.MSP_PROJ_SECURITY e ON (d.PROJ_ID = e.PROJ_ID AND @@SPID = e.SEC_SPID AND e.SEC_READCOUNT > 0)
                  WHERE d.RES_EUID > 0)
WITH CHECK OPTION
GO

GRANT  SELECT  ON [dbo].[MSP_RESOURCES_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_RESOURCES_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_RESOURCES_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_RESOURCES_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_RESOURCES_RES_WRITEVIEW
AS
SELECT *
FROM MSP_RESOURCES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3) AND
(RES_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0) OR
RES_UID <= 0)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_RESOURCES_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_STRING_TYPES_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_STRING_TYPES_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_STRING_TYPES_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_STRING_TYPES_RES_READVIEW
AS
SELECT *
FROM MSP_STRING_TYPES
GO


GRANT  SELECT  ON [dbo].[MSP_STRING_TYPES_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_STRING_TYPES_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_STRING_TYPES_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_STRING_TYPES_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_STRING_TYPES_RES_WRITEVIEW
AS
SELECT *
FROM MSP_STRING_TYPES
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_STRING_TYPES_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TASK_BASELINES_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TASK_BASELINES_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TASK_BASELINES_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_TASK_BASELINES_RES_READVIEW
AS
SELECT *
FROM MSP_TASK_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_TASK_BASELINES_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TASK_BASELINES_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TASK_BASELINES_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TASK_BASELINES_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_TASK_BASELINES_RES_WRITEVIEW
AS
SELECT *
FROM MSP_TASK_BASELINES
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_TASK_BASELINES_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TASKS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TASKS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TASKS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_TASKS_RES_READVIEW
AS
SELECT *
FROM MSP_TASKS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_TASKS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TASKS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TASKS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TASKS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_TASKS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_TASKS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_TASKS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TEXT_FIELDS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TEXT_FIELDS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TEXT_FIELDS_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_TEXT_FIELDS_RES_READVIEW
AS
SELECT *
FROM MSP_TEXT_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_TEXT_FIELDS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TEXT_FIELDS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TEXT_FIELDS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TEXT_FIELDS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_TEXT_FIELDS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_TEXT_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_TEXT_FIELDS_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TIMEPHASED_DATA_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TIMEPHASED_DATA_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TIMEPHASED_DATA_RES_READVIEW]
GO


CREATE VIEW dbo.MSP_TIMEPHASED_DATA_RES_READVIEW
AS
SELECT *
FROM MSP_TIMEPHASED_DATA
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3) 
WITH CHECK OPTION
GO


GRANT  SELECT  ON [dbo].[MSP_TIMEPHASED_DATA_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_TIMEPHASED_DATA_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_TIMEPHASED_DATA_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_TIMEPHASED_DATA_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_TIMEPHASED_DATA_RES_WRITEVIEW
AS
SELECT *
FROM MSP_TIMEPHASED_DATA
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3)
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_TIMEPHASED_DATA_RES_WRITEVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_MV_FIELDS_PROJ_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_MV_FIELDS_PROJ_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_MV_FIELDS_PROJ_READVIEW]
GO
 

CREATE VIEW dbo.MSP_MV_FIELDS_PROJ_READVIEW
AS
SELECT *
FROM MSP_MV_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
  AND (PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3 ))
WITH CHECK OPTION
GO
 

GRANT  SELECT  ON [dbo].[MSP_MV_FIELDS_PROJ_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_MV_FIELDS_PROJ_WRITEVIEW
--************************************************************************************************** 

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_MV_FIELDS_PROJ_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_MV_FIELDS_PROJ_WRITEVIEW]
GO
 

CREATE VIEW dbo.MSP_MV_FIELDS_PROJ_WRITEVIEW
AS
SELECT *
FROM MSP_MV_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJ_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0)
  AND ((PROJ_ID NOT IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE=3))
       OR
       (CODE_CATEGORY=1 AND CODE_REF_UID > (SELECT MAX(SEC_RES_UID) FROM MSP_RESGLOBAL_SECURITY))
       OR
       (CODE_CATEGORY=2 AND CODE_REF_UID > (SELECT MAX(SEC_CAL_UID) FROM MSP_RESGLOBAL_SECURITY)))
WITH CHECK OPTION
GO


GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_MV_FIELDS_PROJ_WRITEVIEW]  TO [MSProjectRole]
GO
 
--**************************************************************************************************
--*		MSP_MV_FIELDS_RES_READVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_MV_FIELDS_RES_READVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_MV_FIELDS_RES_READVIEW]
GO
 

CREATE VIEW dbo.MSP_MV_FIELDS_RES_READVIEW
AS
SELECT *
FROM MSP_MV_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE)  WHERE PROJ_TYPE = 3) AND
CODE_REF_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
WITH CHECK OPTION
GO
 

GRANT  SELECT  ON [dbo].[MSP_MV_FIELDS_RES_READVIEW]  TO [MSProjectRole]
GO

--**************************************************************************************************
--*		MSP_MV_FIELDS_RES_WRITEVIEW
--**************************************************************************************************

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_MV_FIELDS_RES_WRITEVIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MSP_MV_FIELDS_RES_WRITEVIEW]
GO


CREATE VIEW dbo.MSP_MV_FIELDS_RES_WRITEVIEW
AS
SELECT *
FROM MSP_MV_FIELDS
WHERE PROJ_ID IN (SELECT PROJ_ID FROM MSP_PROJECTS  WITH (INDEX=I_MSP_PROJECTS_PROJ_TYPE) WHERE PROJ_TYPE = 3) AND
CODE_REF_UID IN (SELECT ERUID FROM MSP_RES_SECURITY WHERE @@SPID = SEC_SPID AND SEC_WRITECOUNT > 0) 
WITH CHECK OPTION
GO
 

GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE ON [dbo].[MSP_MV_FIELDS_RES_WRITEVIEW]  TO [MSProjectRole]
GO


 
-- End of script
 

 

 

