Background Script to remove duplicate records from User Role table

[Ashish Kumar Ag]

Hi ServiceNow Developers,

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

Please Suggest.

Thanks!

[Gurpreet07]

Yes , you may need to add sys_id filter to skip one row but delete other rows

var gr = new GlideRecord('sys_user_has_role');

//gr.addQuery('user.user_name','admin') ;                   //remove comment and test this script for one user

//gr.addQuery('role','itil') ;                                                                         //if you want to test for a role

gr.query();

while(gr.next()){

var gr2 =   new GlideRecord('sys_user_has_role') ;

gr2.addQuery('user',gr.user);

gr2.addQuery('role',gr.role);

gr2.addQuery('sys_id','!=',gr.sys_id);       // skipping 1 row

gr2.deleteMultiple();

}

You could also test for a particular role by adding filter

[Ashish Kumar Ag]

The issue is still the same. Its deleting all the records.

For example: I have 2 records for itil. Above script(while loop) is running for both records. So deleting both.

Earlier i was thinking if 2nd record will be deleted, while loop will not run for 2nd record to delete the 1st one.

However this is happening.

[Gurpreet07]

Confirm that those 2 records belongs to same user having role itil. Ideally the script provided in last reply should work . I tested this on our instance and its working fine.

[andrew_venables · Accepted solution]

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();

     }

}

[Wade Clairmont]

Quick question, then why does the system seem to grant same roles several times from the same group?

See pic attachment for example.