-----------------------------------
-- PDS EffectiveRights Extension --
-----------------------------------
/* Drop Query PDS ER 27101 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_GetStringCountForLCID')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_GetStringCountForLCID
GO

/* Create Query PDS ER 27101 */
CREATE procedure dbo.MSP_PDS_ER_GetStringCountForLCID
   @p0 INT
AS
	SELECT COUNT(*) from MSP_WEB_CONVERSIONS where LANG_ID=@p0
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_GetStringCountForLCID TO MSProjectServerRole
GO


-----------------------------------
/* Drop Query PDS ER 27102 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadSingleUser')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadSingleUser
GO

/* Create Query PDS ER 27102 */
CREATE PROCEDURE dbo.MSP_PDS_ER_ReadSingleUser
	@username nvarchar(510),
	@userid INT
AS
	SELECT WRES_ID, WRES_GUID, RES_NAME
	  from MSP_WEB_RESOURCES
	 where WRES_CAN_LOGIN = 1
	   and WRES_COUNT_LICENSE = 1
	   and ((@userid <=0) or (WRES_ID = @userid ))
           and ((LEN(@username) = 0) or (@username = RES_NAME))
        order by RES_NAME
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadSingleUser TO MSProjectServerRole
GO




-----------------------------------
/* Drop Query PDS ER 27103 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadResources')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadResources
GO

/* Create Query PDS ER 27103 */
CREATE PROCEDURE dbo.MSP_PDS_ER_ReadResources
	@resname nvarchar(510),
	@resid int
AS
	SELECT WRES_ID, RES_NAME
	  from MSP_WEB_RESOURCES
	 where wres_is_enabled = 1
           and ((Len(@resname)=0) or (RES_NAME=@resname))
           and ((@resid<=0) or (WRES_ID=@resid))
        order by RES_NAME
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadResources TO MSProjectServerRole
GO




-----------------------------------
/* Drop Query PDS ER 27104 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadProjects')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadProjects
GO

/* Create Query PDS ER 27104 */
CREATE PROCEDURE dbo.MSP_PDS_ER_ReadProjects
	@projname varchar(510),
	@projid int
AS
    select WPROJ_ID, PROJ_NAME 
      From MSP_WEB_PROJECTS  
     Where WPROJ_TYPE<3 and WPROJ_DEFAULT_WPROJ_ID=WPROJ_ID 
           and ((Len(@projname)=0) or (PROJ_NAME=@projname))
           and ((@projid<=0) or (WPROJ_ID=@projid))
  order by WPROJ_ID
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadProjects TO MSProjectServerRole
GO

--exec MSP_PDS_ER_ReadProjects N'100X DVD Drive',103

-----------------------------------
/* Drop Query PDS ER 27105 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadPermissionsForGroup')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadPermissionsForGroup
GO

/* Create Query PDS ER 27105 */
CREATE procedure dbo.MSP_PDS_ER_ReadPermissionsForGroup
   @sGroupGUID NVARCHAR(64),
   @nLCID int
AS
	select SCP.WSEC_FEA_ACT_ID, 
		CV.CONV_STRING as WSEC_FEA_ACT_NAME, 
		SCP.WSEC_ALLOW, 
		SCP.WSEC_DENY 
	from MSP_WEB_SECURITY_SP_CAT_PERMISSIONS SCP, 
		MSP_WEB_SECURITY_SP_CAT_RELATIONS SCR ,
		MSP_WEB_CONVERSIONS CV, 
		MSP_WEB_SECURITY_FEATURES_ACTIONS FA 
	where SCR.WSEC_SP_GUID = @sGroupGUID
		and SCR.WSEC_CAT_ID = 0
		and SCR.WSEC_REL_ID = SCP.WSEC_REL_ID 
		and SCP.WSEC_FEA_ACT_ID = FA.WSEC_FEA_ACT_ID 
		and FA.WSEC_FEA_ACT_NAME_ID=CV.CONV_VALUE 
		and CV.LANG_ID=@nLCID
	order by SCP.WSEC_FEA_ACT_ID

RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadPermissionsForGroup TO MSProjectServerRole
GO





-----------------------------------
/* Drop Query PDS ER 27106 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadCategoriesForGroup')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadCategoriesForGroup
GO

/* Create Query PDS ER 27106 */
CREATE procedure dbo.MSP_PDS_ER_ReadCategoriesForGroup
   @sGroupGUID NVARCHAR(64)
AS
	select SC.*  
	from MSP_WEB_SECURITY_SP_CAT_RELATIONS SCR,
		MSP_WEB_SECURITY_CATEGORIES SC  
	where SCR.WSEC_SP_GUID = @sGroupGUID
		and SCR.WSEC_CAT_ID = SC.WSEC_CAT_ID 
	order by SC.WSEC_CAT_NAME 
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadCategoriesForGroup TO MSProjectServerRole
GO




-----------------------------------
/* Drop Query PDS ER 27107 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadCategoryPermissionsForGroup')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadCategoryPermissionsForGroup
GO

/* Create Query PDS ER 27107 */
CREATE procedure dbo.MSP_PDS_ER_ReadCategoryPermissionsForGroup
   @sGroupGUID NVARCHAR(64)
AS
   select SCR.WSEC_CAT_ID,  
	  SCP.WSEC_FEA_ACT_ID,  
	  SCP.WSEC_ALLOW,  
	  SCP.WSEC_DENY  
     from MSP_WEB_SECURITY_SP_CAT_PERMISSIONS SCP,  
	  MSP_WEB_SECURITY_SP_CAT_RELATIONS SCR,  
	  MSP_WEB_SECURITY_FEATURES_ACTIONS FA  
    where SCR.WSEC_SP_GUID = @sGroupGUID
      and SCR.WSEC_CAT_ID<>0 
      and SCR.WSEC_REL_ID = SCP.WSEC_REL_ID  
      and SCP.WSEC_FEA_ACT_ID = FA.WSEC_FEA_ACT_ID 
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadCategoryPermissionsForGroup TO MSProjectServerRole
GO

--  exec MSP_PDS_ER_ReadCategoryPermissionsForGroup N'{A9980F42-1D06-485B-A9BB-ED6DC040B268}'




-----------------------------------
/* Query PDS ER 27108 */
-- is a call to MSP_WEB_SP_QRY_GetAllCategories




-----------------------------------
/* Drop Query PDS ER 27109 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadObjectRulesForCategory')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadObjectRulesForCategory
GO

/* Create Query PDS ER 27109 */
CREATE procedure dbo.MSP_PDS_ER_ReadObjectRulesForCategory
   @nCatID int,
   @nResOrProj int -- 1 is project rules, 2 is resource rules
AS
	select WSEC_OBJ_RULE_TYPE  
	  from MSP_WEB_SECURITY_CATEGORY_RULES  
	 where WSEC_CAT_ID =  @nCatID
	   and WSEC_OBJ_TYPE = @nResOrProj
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadObjectRulesForCategory TO MSProjectServerRole
GO



            



-----------------------------------
/* Drop Query PDS ER 27110 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadSpecialResourcesForCategory')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadSpecialResourcesForCategory
GO

/* Create Query PDS ER 27110 */
CREATE procedure dbo.MSP_PDS_ER_ReadSpecialResourcesForCategory
   @nCatID int
AS
	select WRES_ID, RES_NAME 
	  from MSP_WEB_SECURITY_CATEGORY_OBJECTS, 
	       MSP_WEB_RESOURCES 
	 where WSEC_CAT_ID = @nCatID
	   and WSEC_OBJ_TYPE = 2 
	   and WSEC_OBJ_ID = WRES_ID 
	   and NOT (WRES_IS_ENABLED=0 and WRES_CAN_LOGIN=0 and WRES_COUNT_LICENSE=0 and RES_EUID IS NULL) 
	 Order By RES_NAME 
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadSpecialResourcesForCategory TO MSProjectServerRole
GO




            

-----------------------------------
/* Drop Query PDS ER 27111 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadProjectsInCategory')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadProjectsInCategory
GO

/* Create Query PDS ER 27111 */
CREATE procedure dbo.MSP_PDS_ER_ReadProjectsInCategory
   @nCatID int
AS
       select P.WPROJ_ID,
	      P.PROJ_NAME 
	 from MSP_WEB_PROJECTS P  
   INNER JOIN MSP_WEB_SECURITY_CATEGORY_OBJECTS O
	   ON O.WSEC_OBJ_ID = P.WPROJ_ID  
	Where O.WSEC_OBJ_TYPE = 1
	  and O.WSEC_CAT_ID = @nCatID
     Order By PROJ_NAME 
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadProjectsInCategory TO MSProjectServerRole
GO




-----------------------------------
/* Query PDS ER 27112 */
-- is the same as 27109




-----------------------------------
/* Drop Query PDS ER 27113 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadCategoriesForUser')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadCategoriesForUser
GO

/* Create Query PDS ER 27113 */
CREATE procedure dbo.MSP_PDS_ER_ReadCategoriesForUser
   @sUserGUID NVARCHAR(64)
AS
	select SC.*  
	from MSP_WEB_SECURITY_SP_CAT_RELATIONS SCR,  
		MSP_WEB_SECURITY_CATEGORIES SC  
	where SCR.WSEC_SP_GUID = @sUserGUID
		and SCR.WSEC_CAT_ID = SC.WSEC_CAT_ID 
	order by SC.WSEC_CAT_ID
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadCategoriesForUser TO MSProjectServerRole
GO







-----------------------------------
/* Drop Query PDS ER 27114 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadPermissionsForUser')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadPermissionsForUser
GO

/* Create Query PDS ER 27114 */
CREATE procedure dbo.MSP_PDS_ER_ReadPermissionsForUser
   @sUserGUID NVARCHAR(64),
   @nLCID int
AS
     select SCR.WSEC_CAT_ID, 
	    CV.CONV_STRING as WSEC_FEA_ACT_NAME, 
	    SCP.WSEC_FEA_ACT_ID,  
	    SCP.WSEC_ALLOW,  
	    SCP.WSEC_DENY  
       from MSP_WEB_SECURITY_SP_CAT_PERMISSIONS SCP,   
	    MSP_WEB_SECURITY_SP_CAT_RELATIONS SCR,  
	    MSP_WEB_SECURITY_FEATURES_ACTIONS FA, 
	MSP_WEB_CONVERSIONS CV  
      where SCR.WSEC_SP_GUID = @sUserGUID
	and SCR.WSEC_CAT_ID<>0 
	and SCR.WSEC_REL_ID = SCP.WSEC_REL_ID  
	and SCP.WSEC_FEA_ACT_ID = FA.WSEC_FEA_ACT_ID  
	and FA.WSEC_FEA_ACT_NAME_ID=CV.CONV_VALUE  
	and CV.LANG_ID= @nLCID
   order by WSEC_FEA_ACT_NAME 

RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadPermissionsForUser TO MSProjectServerRole
GO




-----------------------------------
/* Drop Query PDS ER 27115 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_ReadPermissions')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_ReadPermissions
GO

/* Create Query PDS ER 27115 */
CREATE procedure dbo.MSP_PDS_ER_ReadPermissions
   @nLCID int
AS
   select OP.*, CV.CONV_STRING as WSEC_FEA_ACT_NAME , FA.WSEC_ON_OBJECT, FA.WSEC_FEA_ACT_PARENT, FA.WSEC_OBJ_TYPE_ID 
     from MSP_WEB_SECURITY_ORG_PERMISSIONS OP, 
	  MSP_WEB_SECURITY_FEATURES_ACTIONS FA, 
	  MSP_WEB_CONVERSIONS CV 
    where OP.WSEC_FEA_ACT_ID = FA.WSEC_FEA_ACT_ID 
      and FA.WSEC_FEA_ACT_NAME_ID=CV.CONV_VALUE 
      and CV.LANG_ID=@nLCID
    order by OP.WSEC_FEA_ACT_ID
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_ReadPermissions TO MSProjectServerRole
GO





-----------------------------------
/* Drop Query PDS ER 27116 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_GetProjectsManagedByUser')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_GetProjectsManagedByUser
GO

/* Create Query PDS ER 27116 */
CREATE procedure dbo.MSP_PDS_ER_GetProjectsManagedByUser
   @nUserID int
AS     -- (SQL code lifted from MSP_WEB_FN_SEC_GetAllProjectsResCanViewByViewID)

    ( 
       select WPROJ_ID 
	 from MSP_WEB_PROJECTS 
        where WRES_ID = @nUserID
	  and WPROJ_IS_NONWORKING=0 
          and WPROJ_TYPE<>3 
     ) 
     union
     ( 
       select WA.WPROJ_ID 
         from MSP_WEB_ASSIGNMENTS WA,
	      MSP_WEB_PROJECTS WP 
        where WA.WRES_ID_MGR=@nUserID
	  and WA.WPROJ_ID=WP.WPROJ_ID
	  and WP.WPROJ_IS_NONWORKING=0 
	  and WP.WPROJ_TYPE<>3 
     )
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_GetProjectsManagedByUser TO MSProjectServerRole
GO



-----------------------------------
/* Drop Query PDS ER 27117 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_GetProjectsAssignedToUser')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_GetProjectsAssignedToUser
GO

/* Create Query PDS ER 27117 */
CREATE procedure dbo.MSP_PDS_ER_GetProjectsAssignedToUser
   @nUserID int
AS     -- (SQL code lifted from MSP_WEB_FN_SEC_GetAllProjectsResCanViewByViewID)
       ( 
	select A.WPROJ_ID 
	  from MSP_WEB_ASSIGNMENTS A, 
	       MSP_WEB_PROJECTS P 
	 where A.WRES_ID= @nUserID
	   and A.WPROJ_ID=P.WPROJ_ID 
	   and P.WPROJ_IS_NONWORKING=0 
	   and P.WPROJ_TYPE<>3 
       ) 
       union 
       ( 
	   select WP.WPROJ_ID 
	     from MSP_RESOURCES ER  
	INNER JOIN MSP_WEB_RESOURCES WR ON (WR.WRES_ID=@nUserID and WR.RES_EUID=ER.RES_EUID) 
	INNER JOIN MSP_WEB_PROJECTS WP ON (ER.PROJ_ID=WP.PROJ_ID) 
       )
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_GetProjectsAssignedToUser TO MSProjectServerRole
GO



-----------------------------------
/* Drop Query PDS ER 27118 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_GetProjectsManagedByUserDirectReports')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_GetProjectsManagedByUserDirectReports
GO

/* Create Query PDS ER 27118 */
CREATE procedure dbo.MSP_PDS_ER_GetProjectsManagedByUserDirectReports
   @nUserID int
AS     -- (SQL code lifted from MSP_WEB_FN_SEC_GetAllProjectsResCanViewByViewID)

     ( 
     select WPROJ_ID 
       from MSP_WEB_PROJECTS 
      where WPROJ_IS_NONWORKING=0 
	and WPROJ_TYPE<>3 
	and WRES_ID in ( 
	    select WRES_ID from dbo.MSP_WEB_FN_SEC_GetResourcesManagedByResID(@nUserID, 0, -1)  
	) 
    ) 
    UNION 
    ( 
     select WA.WPROJ_ID 
       from MSP_WEB_ASSIGNMENTS WA, 
	    MSP_WEB_PROJECTS WP 
      where WA.WPROJ_ID=WP.WPROJ_ID 
	and WP.WPROJ_IS_NONWORKING=0 
	and WP.WPROJ_TYPE<>3 
	and WA.WRES_ID_MGR in ( 
	select WRES_ID from dbo.MSP_WEB_FN_SEC_GetResourcesManagedByResID(@nUserID, 0, -1)  
	) 
    ) 
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_GetProjectsManagedByUserDirectReports TO MSProjectServerRole
GO



-----------------------------------
/* Drop Query PDS ER 27119 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_GetProjectsAssignedToUserDirectReports')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_GetProjectsAssignedToUserDirectReports
GO

/* Create Query PDS ER 27119 */
CREATE procedure dbo.MSP_PDS_ER_GetProjectsAssignedToUserDirectReports
   @nUserID int
AS     -- (SQL code lifted from MSP_WEB_FN_SEC_GetAllProjectsResCanViewByViewID)
    ( 
	select A.WPROJ_ID 
	  from MSP_WEB_ASSIGNMENTS A, 
	       MSP_WEB_PROJECTS P 
	 where A.WPROJ_ID=P.WPROJ_ID 
	   and P.WPROJ_IS_NONWORKING=0 
	   and P.WPROJ_TYPE<>3 
	   and A.WRES_ID in ( 
	       select WRES_ID from dbo.MSP_WEB_FN_SEC_GetResourcesManagedByResID(@nUserID , 0, -1)  
	   ) 
    ) 
    UNION 
    ( 
	select WP.WPROJ_ID  
	  from MSP_WEB_PROJECTS WP, 
	       MSP_WEB_RESOURCES WR, 
	       MSP_RESOURCES ER 
	 where ER.PROJ_ID=WP.PROJ_ID 
	   and WR.RES_EUID=ER.RES_EUID 
	   and WR.WRES_ID in ( 
	       select WRES_ID from dbo.MSP_WEB_FN_SEC_GetResourcesManagedByResID(@nUserID , 0, -1)  
	   ) 
    ) 

RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_GetProjectsAssignedToUserDirectReports TO MSProjectServerRole
GO




-----------------------------------
/* Drop Query PDS ER 27120 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_GetResourcesUserAssignsTasksTo')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_GetResourcesUserAssignsTasksTo
GO

/* Create Query PDS ER 27120 */
CREATE procedure dbo.MSP_PDS_ER_GetResourcesUserAssignsTasksTo
   @nUserID int
AS
	select distinct WRES_ID from MSP_WEB_ASSIGNMENTS where WRES_ID_MGR=@nUserID and WRES_ID<>-1 
	union 
	select distinct WR.WRES_ID 
	 from MSP_WEB_PROJECTS WP INNER JOIN MSP_RESOURCES ER ON (WP.PROJ_ID=ER.PROJ_ID and ER.RES_EUID IS NOT NULL) 
	      INNER JOIN MSP_WEB_RESOURCES WR ON (ER.RES_EUID=WR.RES_EUID) 
	where WP.WRES_ID=@nUserID or WP.WPROJ_ID in ( 
		select WP2.WPROJ_ID  
		  from MSP_WEB_PROJECTS WP2 INNER JOIN MSP_WEB_ASSIGNMENTS WA on WA.WPROJ_ID=WP2.WPROJ_ID 
		 where WA.WRES_ID_MGR=@nUserID)
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_GetResourcesUserAssignsTasksTo TO MSProjectServerRole
GO




-----------------------------------
/* Drop Query PDS ER 27121 */
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_GetResourcesAndDirectReportsOfUser')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_PDS_ER_GetResourcesAndDirectReportsOfUser
GO

/* Create Query PDS ER 27121 */
CREATE procedure dbo.MSP_PDS_ER_GetResourcesAndDirectReportsOfUser
   @nUserID int,
   @nMaxLevel int
AS
  SELECT WRES_ID from MSP_WEB_FN_SEC_GetResourcesManagedByResID(@nUserID, 0, @nMaxLevel)
RETURN
GO

GRANT EXECUTE ON dbo.MSP_PDS_ER_GetResourcesAndDirectReportsOfUser TO MSProjectServerRole
GO


-----------------------------------
/* Update Query PDS ER */
-- Set the object types for the object level permissions 
-- that apply to projects to 1 and resources to 2
update MSP_WEB_SECURITY_FEATURES_ACTIONS set WSEC_OBJ_TYPE_ID=1 where WSEC_FEA_ACT_ID in (213,508,509,512,781,1012,1013,1019)
update MSP_WEB_SECURITY_FEATURES_ACTIONS set WSEC_OBJ_TYPE_ID=2 where WSEC_FEA_ACT_ID in (212,510,513,1014,1015,1020)

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'MSP_PDS_ER_GetResourcesAndDirectReportsOfUser'
AND ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_SCHEMA='dbo')

IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_PDS_ER_GetResourcesAndDirectReportsOfUser')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

quote:

---

Originally posted by MichaelLehzen

Do you mean that I should edit and replace the relevant parts of the script, as you described above ?

---

quote:

---

Originally posted by MichaelLehzen

o.k., I will try. It will take some time, so I will be back in a few minutes ...

---

quote:

---

Originally posted by MichaelLehzen

I exchanged all statements within the file, without success. The error message is still the same.

---

quote:

---

Originally posted by MichaelLehzen

I did (my first reply). The answer is: Yes, I have this column in the table.

---

quote:

---

Originally posted by MichaelLehzen

Msg 207, Level 16, State 1, Procedure MSP_PDS_ER_ReadResources, Line 9
Invalid column name 'wres_is_enabled".
Msg 15151, Level 16, State 1, Line 2
Cannot find the object "MSP_PDS_ER_ReadRessources', because it does not exist or you do not have permission.

---

quote:

---

Originally posted by MichaelLehzen

visakh16:
Collation for the database is set to German_Phonebook_BIN

---

CREATE PROCEDURE dbo.MSP_PDS_ER_ReadResources
	@resname nvarchar(510),
	@resid int
AS
	SELECT WRES_ID, RES_NAME
	  from MSP_WEB_RESOURCES
	 where WRES_IS_ENABLED  wres_is_enabled = 1
           and ((Len(@resname)=0) or (RES_NAME=@resname))
           and ((@resid<=0) or (WRES_ID=@resid))
        order by RES_NAME
RETURN
GO