How to update the roles field with all the roles associated to the user

[User206787]

Hi All, 

There is a field called roles on the sys_user table which is a list collector, Now we also have a table called sys_user_has_role table where all the roles associated to the user are stored. 

Can someone provide me a fix script which can look at all the roles associated to a user and update the same to the roles field in the sys_user table which is a list collector?

How can this be implemented in a  flow designer as well? If flow designer has a way to update the roles table with the roles added to the sys_user_has_roles table that would be awesome as well. 

Please let me know in case anyone has a solution (Script) to this

Thanks,

Praveen.

[Saurabh Gupta]

Hi,
Roles field is not list collector type, this is OOTB field of type "User Roles". I don't think we should be updating this field with roles from sys_user_has_role table.

There is an internal link between role and this field.
Try below link to check
https://yourinstance.service-now.com/sys_user_list.do?sysparm_query=roles=admin

[Community Alums]

Hi @User206787 ,

Please find below script. Though I would suggest you not to update the roles field of user table. There might be other scripts running on that field which may get impacted.

First Way:

var user = new GlideRecord("sys_user");

user.query();

while (user.next()) {

    var userRoleArr = [];

    var hasRoleGR = new GlideRecord("sys_user_has_role");

    hasRoleGR.addEncodedQuery("user=" + user.sys_id);

    hasRoleGR.query();

    while (hasRoleGR.next()) {

        userRoleArr.push(hasRoleGR.getValue("role"));

    }

    if (userRoleArr.length > 0) {

        user.roles = userRoleArr.join(',');

        user.update();

    }

}

Second Way: (Run this is background script first)

var hasRoleGR = new GlideAggregate('sys_user_has_role');

hasRoleGR.groupBy('user');

hasRoleGR.addAggregate('GROUP_CONCAT_DISTINCT', 'role');

hasRoleGR.query();

while (hasRoleGR.next()) {

    var user = hasRoleGR.getValue('user');

    var userDV = hasRoleGR.getDisplayValue('user');

    var gcd = hasRoleGR.getAggregate('GROUP_CONCAT_DISTINCT', 'role')

    gs.info('User Name: ' + userDV + ' ## User Sys ID: ' + user + ' ## Roles: ' + gcd);

}

Please mark the answer as correct and helpful, if I was able to answer your query. It will be helpful for others who are looking for similar questions.

Thank you,

Santosh Poudel

[OlaN]

Hi,

Like Saurabh already mentioned, you should not make changes to this OOB field Roles.

What are you trying to do? What is it you want to achieve? What issue are you trying to solve?