Delete orphan records from sys_user_has_role table

[gee]

Hi Team,

I have an issue with deleting orphan records from sys_user_has_role table.

Tried with fix script to delete these records using GlideRecord, failed.

Delete button is disabled, even though schema level deletion is enable.

Also dried to deleted the user and imported the same user record using XML (user record export). Still role definitions are there!

Is there any method to delete these records from the above table?

Any suggestion please?

Platform is on Helsinki Patch 8.

Thanks & Regards,

Gee Abraham

[Rajshekhar Pau1]

gee

Hi Gee,
Have you tried this one?

In my example script, I have used a dummy user 'Bernard Laboy' in this line--   users.addQuery('user_name', 'bernard.laboy');

Please review this accordingly and give a try.

Kind Regards,

Rajshekhar Paul

[gee]

Hi All,

Sorry, I have tried these scripts and it did NOT worked.

Thanks & Regards,

Gee

[nitish99]

Hi Gee,

Couldn't help but notice that you have Domain Separation in the instance.

Try selecting the global domain and then running the below script in Background Scripts-:

var grpSysID = "sys_id of the Group";

var _user = new GlideRecord('sys_user');

_user.addQuery('user_name', 'david.loo');

_user.query();

if(_user.next()){

      var _userGrp = new GlideRecord('sys_user_grmember');

      _userGrp.addQuery('user', _user.sys_id);

      _userGrp.addQuery('group', grpSysID);

      _userGrp.query();

if(_userGrp.next()) {

            _userGrp.delete();

      }

}

else{

var _userRole = new GlideRecord('sys_user_has_role');

      _userRole.addQuery('user', _user.sys_id);

      _userRole.query();

while(_userRole.next()) {

gs.print(_userRole .getRowCount());

gs.print('********* sys_user_has_role ' + _userRole.getValue('role'));

_userRole.deleteMultiple();

      }

}

[gee]

Hi Rajshekhar,

Please see the updated script below. Thanks & Regards, Gee

---

// var usr = new GlideAggregate("sys_user_has_role");

// usr.addQuery("user", "7225d7164f03a2007b3be6518110c792");

// usr.query();

// while(usr.next())

//         {

//                 usr.setWorkflow(false);

//                 usr.deleteMultiple();

//                 gs.log('********* sys_user_has_role ' + usr.role.name + ' ' + usr.granted_by);

//         }

var users = new GlideRecord('sys_user');  

users.addQuery('user_name', 'yatsu@jp.fujitsu.com');  

users.query();  

if(users.next()){  

      var hasRole = new GlideRecord('sys_user_has_role');  

      hasRole.addQuery('user', users.sys_id);

      hasRole.query();  

while(hasRole.next()) {

gs.print(hasRole.getRowCount());

gs.print('********* sys_user_has_role ' + hasRole.role.name);

hasRole.deleteMultiple();  

      }  

}  

---

[Rajshekhar Pau1]

Hi Gee,

Did that work for you?

Is the email id on sys_user table has field name 'user_name'?

users.addQuery('user_name', 'yatsu@jp.fujitsu.com');

Please check if that works.

Regards,

Rajshekhar Paul