force rebuild user role table

[twooton]

I am about to embark on the task of cleaning up user granted roles directly in some cases and role changes in groups. I will be consolidating down duplicated roles given and removing roles directly given to users. I have seen the behavior of a user not receiving a role that is granted to a group after the user is already member and other weird behavior with users belonging to a group yet not having the record in the sys_user_has_role of the role granted to the group. This has been a slight irritation in the past, but is going to be a big headache now with the changes I need to make.

So to put it simply, is their a way to force a rebuild of the sys_user_has _role table? If not, then is their a better method beyond manually removing all roles and groups and re-adding the appropriate group(s) to an individual user record?

[adamjgreenberg]

You can rebuild the table by running the glide table manager, invalidate table cache against the table you need to flush within scripts background. If you don't have access to this module, you'll need your instance administrator to run this.

GlideTableManager.invalidateTable("TABLE_NAME");

https://docs.servicenow.com/bundle/geneva-release-notes/page/release-notes/r_Geneva-Patch-5-HF-4-PO.html

[twooton]

My apologies, a bit of clarification. I am not rebuilding table structure, but the records within the table. There are records in the sys_user_has_role table which should have been removed upon role changes to a group, and records that should have been created that were not.

[adamjgreenberg]

There is no automated way to clean up this table, it has to be cleaned up manually.

[dvp]

Here is the script that identifies the duplicate records in sys_user_has_role table.

• This script will return all the records that have same user and role. Once you are comfortable and validated, then we can update the script to delete one of the duplicates.

var gr = new GlideRecord("sys_user_has_role");
gr.query();
while (gr.next()) {
   var usr_role = new GlideRecord("sys_user_has_role");
	usr_role.addQuery('user', gr.user);
	usr_role.addQuery('role', gr.role);
	usr_role.query();
	
	if(usr_role.getRowCount() >1 ){
		gs.log('User: ' + gr.user.getDisplayValue() + ' || role: ' + gr.role.getDisplayValue());
	}

}