How to: assign a group to users via SQL

Version 1

    Verified Product Versions

    Service Desk 7.7.xService Desk 7.8.xService Desk 2016.xAsset Manager 2016.xAsset Manager 2017.xService Desk 2017.x



    In this document we will review how to assign a group to a user using a SQL script.

    You may need this to add groups that have not been imported after an Active Directory or LDAP import.


    This works from 7.7.x versions up to the most recent 2017.1


    From SQL side:


    Adding a group form the software side does correspond to adding a line in the tps_user_group in the Database.

    This table does store all the needed information, such as primary keys of users and groups.
    The total amount of column is present in the table is 14, however only 5 of these need to be fulfilled:

    • tps_user_id
    • tps_group_id
    • tps_creation_user_guid
    • tps_last_update_user_guid
    • tps_creation_date




    Firstly you will need to retrieve two of the information mentioned in the list above: the guid of the group that you want to add and the one of the user.

    To do so, you will need to user the following scripts:


    select tps_guid from tps_group where tps_title = 'InsertHereTheGroupName'

    select tps_guid from tps_user where tps_title = 'InsertHereTheUserName'


    After having run this, take the two obtained guids and substitute them properly in the second script.

    Please mind that parameters number 3 and 4 have to indicate the guid of a user that has privileges to add groups, ideally SA or an analyst.


    insert into tps_user_group (tps_user_id, tps_group_id, tps_creation_user_guid, tps_last_update_user_guid, tps_creation_date)

    values ('1', '2', '3','4', getdate())


    1. change this with target user guid
    2. change this with the guid of the group you need to add
    3. change this with SA user guid
    4. change this with SA user guid


    Done this the group is added to the user.
    The script can eventually be modified and applied to multiple users per one group using the proper selection statements.