Unable to delete records from sys_user_has_role table

[Ankit Jain5]

I have a custom requirement wherein we need to remove the itil role for the users who have not logged in from pas 9o days.

Below is my script which is not working in a scheduled job.

var gr1 = new GlideRecord("sys_user_has_role");
gr1.addEncodedQuery('user.last_login_time<javascript:gs.beginningOfLast3Months()^role=282bf1fac6112285017366cb5f867469');
gr1.setLimit(5);
gr1.query();
gr1.inherited.setValue(false);
gr1.setWorkflow(false);
gr1.update();
gr1.deleteRecord();

Role Management plugins are already installed, Role Management Enhancement plugin I am unable to search.

[Mark Roethof]

Hi there,

While roles directly to users is a bad practice, I will try to answer your question:

If it's about deleting, why then updating a value first? And update and delete on what? You do a query without a next?

I also would try if possible, to use as much as possible updateMultiple and deleteMultiple.
In your case:

var gr1 = new GlideRecord("sys_user_has_role");
gr1.addEncodedQuery('user.last_login_time<javascript:gs.beginningOfLast3Months()^role=282bf1fac6112285017366cb5f867469');
gr1.setLimit(5);
gr1.query();
gr1.deleteMultiple();

Do be aware: this only works for NOT inherited roles!!!

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark

---

LinkedIn
Community article list

[Harsh Vardhan · Accepted solution]

You can not perform delete and update method same time . first update the value to inherited as false and then perform delete operation.

Update code:

var gr1 = new GlideRecord("sys_user_has_role");
gr1.addEncodedQuery('user.last_login_time<javascript:gs.beginningOfLast3Months()^role=282bf1fac6112285017366cb5f867469');
gr1.setLimit(5);
gr1.query();

while(gr1.next()){
gr1.inherited.setValue(false);

gr1.update();

}

delete code:

var gr1 = new GlideRecord("sys_user_has_role");
gr1.addEncodedQuery('user.last_login_time<javascript:gs.beginningOfLast3Months()^role=282bf1fac6112285017366cb5f867469');
gr1.setLimit(5);
gr1.query();

gr1.deleteMultiple();

[Ankit Jain5]

Thanks man.. it worked..

[Ram158]

Thanks alot brother, it helped me alot.