How to: assign a group to users via SQL

Version 1

    Verified Product Versions

    LANDESK Service Desk 7.7.xLANDESK Service Desk 7.8.xLANDESK Service Desk 2016.xLANDESK Asset Central 2016.xLANDESK Asset Central 2017.xLANDESK Service Desk 2017.x

    Purpose:

     

    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

     

    Script:

     

    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.