Background Script to remove duplicate records from User Role table

Ashish Kumar Ag · ‎08-18-2015

Hi ServiceNow Developers,

I want to remove the existing duplicate records from User Role table(sys_user_has_role) table.

Please Suggest.

Thanks!

andrew_venables · ‎08-19-2015

Firstly don't use a variable names "gr" in a Background Script. There is a known issue with this.

Secondly, you should consider whether this is something you really should be doing?

The platform deliberately creates multiple sys_user_has_role records, it's be design, so by trying to fix you are breaking something else.

For example if John is a member of the Network team and the Database team, he may have the itil role twice (two records in the sys_user_has_role table). Each one will track which group assigned him the role. If you hack the platform so he only has one record, let's say you delete the one corresponding to the Network team, he is only left with the one corresponding to the Database team. If John is then removed from the Database team the platform will remove his remaining sys_user_has_role record and he won't have the itil role anymore even tho he is still in the Networks team!

Thirdly your code is pretty inefficient, optimise it with GlideAggregate:

// Group the user role records by user/role combo


var userRoleGA = new GlideAggregate('sys_user_has_role');


userRoleGA.groupBy('user');


userRoleGA.groupBy('role');


userRoleGA.query();


var userRoleGR;


while (userRoleGA.next()) {


     // Fetch the records for this user/role pair


     userRoleGR = new GlideRecord('sys_user_has_role');


     userRoleGR.addQuery('user', userRoleGA.user);


     userRoleGR.addQuery('role', userRoleGA.role);


     userRoleGR.query();


     userRoleGR.next(); // Skip the first result


     while (userRoleGR.next()) { // delete the rest


             userRoleGR.deleteRecord();


     }


}

View solution in original post

Kalaiarasan Pus · ‎08-19-2015

Are the roles added twice on its own or the user belongs to more than one group and gets the duplicate roles using that? If the case is the latter, it would be wise to leave it as is.

faisal_dadabhoy · ‎03-09-2017

I agree with "Kalaiarasan P". Unless you really know what you are doing the approach does not look right.

Here is a scenario where, it would cause a major issue? (unless I am missing something or not testing correctly):

Lets say:

John_doe has 3 ITIL roles granted by below groups

Group1

Group2

Group3

The script took away the role that was granted by Group2 and Group3

Afterwards John_doe was removed from Group1.

Now John_doe does not have any ITIL roles; even though, he is still part of Group2, Group3.

Thanks, Faisal