Resolution
MS Project Server does not allow project managers to
put their projects into a security category. We overcome this shortcoming by
creating a custom enterprise Project flag field and adding a SQL trigger to the
msp_flag_fields table.
We will call the flag field “Department A Only”.
If this value is true, then the project belongs in the “Department A” category.
If it is false, the project does not belong in this category.
After you create the custom field and security
category, look up the conv_value in the
msp_conversions table.
Then look up the value of wsec_cat_id in the
msp_web_security_categories table.
The values for my example are 188744649 and 101 respectively.
Finally, create the following trigger on the
msp_flag_fields table and test.
CREATE TRIGGER [Category] ON [dbo].[MSP_FLAG_FIELDS]
FOR INSERT, UPDATE
AS
declare @Const_flag_field as
integer
select @Const_flag_field=188744649 -- Value
of controlling flag from msp_conversions
declare @Const_wsec_cat_id
as integer
select @Const_wsec_cat_id=101
--ID of category from msp_web_security_categories
declare @flag_field_id as
integer
declare @flag_value as integer
declare @wproj_id
as integer
Select @flag_field_id=i.flag_field_id,
@flag_value=flag_value, @wproj_id=p.wproj_id
from inserted i
inner join msp_web_projects p on
i.proj_id=p.proj_id
if @flag_field_id=@Const_Flag_Field
Begin
if @flag_value=1
Begin
Insert into msp_web_security_category_objects
values (@Const_wsec_cat_id, 1, @wproj_id)
End
Else
Begin
delete from msp_web_security_category_objects
where wsec_cat_id=@Const_wsec_cat_id
and wsec_obj_type=1
and wsec_obj_id=@wproj_id
end
end