Cannot set Data Partitioning by Customer on Process and Problem levels

Version 2

    Verified Product Versions

    Service Desk 7.8.xService Desk 2016.x


    Service Desk versions 7.3.2 up to 7.8.1 at time of writing.


    Requires Access To:

    Database Server


    When setting up data partitioning in Service Desk, if the customer partitioning attribute is set to Customer at the Process level you cannot change it at Incident and Problem level. This is fine for Incident. If you set the partitioning attribute to Customer on the Process, it will be set for Customer on the Incident. However the attribute on Process with the title 'Customer' is actually named '_Customer1' and does not inherit the partitioning. There is another attribute with title 'User2' named 'Customer' that does inherit the partitioning for Customer but this attribute is a User relationship not a Customer Group relationship. As this attribute is named 'Customer' it inherits the customer partitioning from 'Customer' on the Process but will never match as it points to the User object.


    It appears that you can unset this as being the partitioning attribute but you cannot then set any other attribute to be the customer partitioning attribute. Even if you add another customer attribute you cannot name it Customer and will not allow customer partitioning.


    If you do not set partitioning attribute at Process level, then Process is not partitioned and queries etc on the Process object will allow Process details to be seen by all users.


    Therefore it is currently not possible to set customer partitioning to Customer on Process and Problem.  This has been raised as a Problem reference 4838 and if you encounter this issue please raise with support.


    Solution / Workaround:

    There is a workaround for this which is to run the following SQL script on the database which will point the 'User2' attribute to the Customer object rather than User.  You can then change the title of 'User2' to Customer if you wish and 'Customer' to 'Customer1' for clarity.  This is for SQL Server platforms only.  NOTE: Please make sure you test this first on a copy of the database and take a full backup before applying live.


    ALTER TABLE [dbo].[pr_problem] DROP CONSTRAINT [fkey$pr_problem$prb_cus]
    ALTER TABLE [dbo].[pr_problem] WITH CHECK ADD CONSTRAINT [fkey$pr_problem$prb_cus] FOREIGN KEY([pr_customer_guid])
    REFERENCES [dbo].[tps_customer] ([tps_guid])
    ALTER TABLE [dbo].[pr_problem] CHECK CONSTRAINT [fkey$pr_problem$prb_cus]
    update md_attribute_type set md_rel_class_type_guid = 'cf9cf657-c1dc-4f69-b340-7fc4496d5606' where md_guid = 'b3cbb5a2-f8b8-457e-9bcc-f38e23d1237a';
    update md_foreign_key set md_tgt_database_table_guid = '4423fb87-d7de-4e7d-9b80-2d30b6462b36' where md_guid = '2c14f697-054e-4aa8-9343-0ce4a52f47f4';


    The changes made to the database using these SQL statements will be reverted if the MDM is run.