Privileges for analysts, roles and groups

Version 2

    Verified Product Versions

    Service Desk 2016.x


    There have been instances where trying to delete unused business objescts causes issues, because analysts have been given privileges against them.  The attached script can be amended or just used to look at the tables used.


    -- Analysis of specific privilege items for specific Roles


    SELECT     tps_role.tps_name, md_privileged_item.md_name, tps_privilege.tps_value

    FROM         tps_privilege INNER JOIN

                          md_privileged_item ON tps_privilege.tps_item_guid = md_privileged_item.md_guid INNER JOIN

                          tps_role ON tps_privilege.tps_collection_guid = tps_role.tps_privilege_collection_guid

    WHERE     (tps_role.tps_name IN ('Analyst', 'PortalAnalyst')) AND (md_privileged_item.md_name IN ('System.User', 'System.Analyst', 'System.GroupType',

                          'System.Group', 'System.SupportGroup'))

    ORDER BY tps_role.tps_name, md_privileged_item.md_name




    -- Select items for a specific Role privileges collection and specific privilege items


    select * from tps_privilege where tps_collection_guid = '3BBDA6CC-1C2B-4B2D-A91C-7E6456B3CB03' and

    tps_item_guid in(select md_guid from md_privileged_item where md_name in('System.User', 'System.Analyst', 'System.GroupType', 'System.Group', 'System.SupportGroup'))




    -- Helps identify Role privilege collection GUIDs and privileged item GUIDs


    select * from tps_role


    select * from md_privileged_item where md_name = 'System.SupportGroup'







    -- Another version


    SELECT     tps_privilege.tps_value, tps_role.tps_name, md_privileged_item.md_name

    FROM         tps_role tps_role INNER JOIN

                          tps_privilege tps_privilege INNER JOIN

                          tps_privilege_collection tps_privilege_collection ON tps_privilege.tps_collection_guid = tps_privilege_collection.tps_guid INNER JOIN

                          md_privileged_item md_privileged_item ON tps_privilege.tps_item_guid = md_privileged_item.md_guid ON

                          tps_role.tps_privilege_collection_guid = tps_privilege_collection.tps_guid

    WHERE     (tps_role.tps_name = 'Administrators')

    ORDER BY md_privileged_item.md_name


    ITBM all versions