Time to time someone may need to remove the "orphaned" user - role assignments from the "sys_user_has_role" table due to cloning or other activities.
I have written a fix script to easily remove this records and also include "inherited" roles which can't be simple deleted.
As I have not found a script in the community yet I like to share it with you.
I recognized a average processing speed of roughly 100.000 records per hour,
please feel free to share your run times for getting more accurate results / calculations
Note: If you actually want to delete the records, set the first variable to false
//set the following variable to false if the records should be actually deleted instead of only counted
var countonly = true;
//first count the "to be deleted" records
var totalamount = get_row_count();
if (!countonly) { //if the above variable is set to true, only count but do not delete anything
var start = new GlideDateTime().getNumericValue(); //set a start timer (usefull for non fix scripts)
update_roles_inherited(); //update the "inherited" roles first so we are able to delete them
delete_roles(); //finally delete all orphaned roles
var totaltime = parseInt(new GlideDateTime().getNumericValue() - start) / 1000 / 60; //calculate the total execution time in minutes
totaltime = Math.round(totaltime);
gs.log("Total execution for updating and removing (whithout counting) of " + totalamount + " records: " + totaltime + " minutes", "Orphaned role cleanup"); //finally log the total amount and execution time
} else { //only estimate the run time
var estimatedtime = totalamount / 1666; //devide the counted records by 1666 (records per min)
estimatedtime = Math.round(estimatedtime);
gs.log("Found " + totalamount + " orphaned records which could be deleted in roughly " + estimatedtime + " minutes", "Orphaned role cleanup"); //finally log the total amount and execution time
}
//for quick counting GlideAggregate is used
function get_row_count() {
var hasrole = new GlideAggregate("sys_user_has_role");
hasrole.addQuery('user.user_name', ''); //user_name is what we check for as is should be there for any user
hasrole.addAggregate("COUNT");
hasrole.query();
if (hasrole.next()) {
return parseInt(hasrole.getAggregate("COUNT"));
}
}
function update_roles_inherited() {
var hasrole = new GlideRecord("sys_user_has_role");
hasrole.addQuery('role.name', '!=', 'admin'); //exclude admin role just in case there is something wrong with the below query
hasrole.addQuery('user.user_name', ''); //user_name is what we check for as is should be there for any user
hasrole.addQuery('inherited', true); //only update "inherited" role assignments
hasrole.setValue('inherited', false);
hasrole.setWorkflow(false); //do not trigger any further scripts for performance
hasrole.updateMultiple(); //update all
}
function delete_roles() {
var hasrole = new GlideRecord("sys_user_has_role");
hasrole.addQuery('role.name', '!=', 'admin'); //exclude admin role just in case there is something wrong with the below query
hasrole.addQuery('user.user_name', ''); //user_name is what we check for as is should be there for any user
hasrole.addQuery('inherited', false); //only delete "non-inherited" role assignments as we can't delete the others
hasrole.setWorkflow(false); //do not trigger any further scripts for performance
hasrole.deleteMultiple(); //delete all
}
