Removing Roles of Inactive users

Mason5 · ‎06-17-2020

Hi All,

I am trying to remove all roles of users(from sys_user_has_role) who are inactive and locked out in user table but it does not allow to remove records from sys_user_has_role table, does not delete with Background script as well.

How can we achieve this, any idea?

Thanks in Advance!

Ankur Bawiskar · ‎06-17-2020

Hi Mason,

ensure you wrap code inside function

updateRecord();

function updateRecord(){

try{

var gr = new GlideRecord('sys_user_has_role');
gr.addEncodedQuery('user.locked_out=true^user.active=false');

gs.info('Row Count is: ' + gr.getRowCount());

gr.query();
while(gr.next())
{
gr.deleteRecord();
}
}

catch(ex){
gs.info('Exception is: ' + ex);
}

}

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

MrMuhammad · ‎06-17-2020

Hi Mason,

You can use below script to remove roles of all the inactive users.

var userGr = new GlideRecord('sys_user');
userGr.addQuery('active', false);
userGr.addQuery('locked_out', true);
userGr.query();

gs.print("TOTAL INACTIVE USERS -" + userGr.getRowCount());

while(userGr.next()){
  var userRoles = new GlideRecord('sys_user_has_role');
  userRoles.addQuery('sys_id',userGr.sys_id); // sys_id of user
  userRoles.query();

  userRoles.deleteMultiple();
}

Please mark this accepted & helpful if it answered your question. Thanks!

Regards,
Muhammad

JJT · ‎07-15-2023

Thank you Mark. I created a flow to delete roles and groups moving forward but needed a way to remove roles from 500 or so already de-activated accounts. Your script worked well. Ran it as a background script from the ALL menu..