Remove associated roles for all users who belong to a specific company

Version 3

    Environment:

     

    All Environments

     

    Question: 

     

    I would like to remove all roles for users who belong to a specific company, they will stay active within Service Desk but I wish for them not to be able to login into Service Desk.

     

    Answer:

     

    This can be achieved by running the below script, Please note that the script is a select statement that as an example will show all user roles for End User members of the "OurCompany" group, excluding SelfServiceGuest and Guest.

     

    Change the value for @UserGroup to see results for different groups, to ensure you are targeting the correct group, then change the select statement to delete.

     

    Please do ensure that before running any scripts on your Database that a Backup has been taken and this is tested first before applying to a Live Database.

     

    ====

    declare @UserGUID uniqueidentifier

    declare @UserGroup nvarchar(64)

    declare @UserGroupGUID uniqueidentifier

    declare @excluded nvarchar(64)

     

    set @UserGroup = 'OurCompany' -- edit this to see roles from other groups

     

     

    select @UserGroupGUID = tps_guid from tps_group where tps_name = @UserGroup

     

    begin

    select * from tps_user_role where tps_user_id in (select tps_user_id from tps_user_group where tps_group_id = @UserGroupGUID)

    and tps_user_id not in (select tps_guid from tps_user where tps_name in ('SelfServiceGuest','Guest'))

    and tps_user_id not in (select tps_guid from tps_user where tps_user_type_guid in ('CC1AD49A-FBDC-4768-A92B-25B48C1374B8','9D3391F6-3235-4A88-A54C-6B322FB714CB'))

    end

    ====