Trouble detecting user role in script

[Mike Hashemi]

I am trying to write a script where I will get all active contacts associated with an account, then if the contact does not have the "itil" role assigned, I will set the user_name field to be blank.

I have the following snippet:

function hasRole(userRecord, roleName) {
    var grRoles = new GlideRecord('sys_user_role');
    grRoles.addQuery('user', userRecord);
    grRoles.addQuery('name', roleName);
    grRoles.query();

	if (grRoles.next()) {
		return true;
	} else {
		return false;
	}
}

var result = hasRole('ffff12e047cee910a69a1e8dd46d435e', 'itil');
gs.info('The result is: {0}', result);

Obviously, "ffff12e047cee910a69a1e8dd46d435e" is the sys_id of a contact. When I run the snippet in background scripts, sometimes I get back:

*** Script: The result is: true

While sometimes I get back: 

QueryEventLogger: Invalid query detected, please check logs for details [Unknown field user in table sys_user_role]
Invalid query detected, stack trace below [Unknown field user in table sys_user_role]

There are two problems.

1. The contact in question, is NOT a member of itil, so the script should be returning "false". If I change the role from "itil" to "asdf", the script returns "false". I looked at the user's eight assigned roles and none of them have "itil" in the Role Contains related list
2. Running the script over and over, without making any changes, sometimes I get the expected string (The result is...) and sometimes I get an error. Not sure what's up with that

What is going on and is there a better way to do this?

[Kris Moncada]

The table sys_user_role does not have a "user" field.

Try using the table sys_user_has_role instead.

[Sandeep Rajput · Accepted solution]

@Mike Hashemi Found a couple of mistakes with your script.

1. Querying the wrong table: Instead of querying sys_user_has_role table, you are querying sys_user_role table. sys_user_role table doesn't have user column into it.

2. Checking name instead of role.name, you should check with role.name instead of the name on line number 4.

Please update the script as follows and see if it yields correct results for you.

function hasRole(userRecord, roleName) {
    var grRoles = new GlideRecord('sys_user_has_role');
    grRoles.addQuery('user', userRecord);
    grRoles.addQuery('role.name', roleName);
    grRoles.query();

	if (grRoles.next()) {
		return true;
	} else {
		return false;
	}
}

Please mark the answer helpful and correct if it manages to address your issue.

[Mike Hashemi]

Thanks.

[Community Alums]

Hi @Mike Hashemi ,

I checked your problem in my PDI and I got to know like in sys_user_role table there user field is not present.

So as per your code every time it goes in else part, so instead of that you can change the table to sys_user_has_role there is user field is present so you can easily make a glideRecord you can refer below script 

function hasRole(userRecord, roleName) {
    var grRoles = new GlideRecord('sys_user_has_role');
    grRoles.addEncodedQuery("role="+ roleName"^user="+userRecord);
    grRoles.query();
	if (grRoles.next()) {
		return true;
	} else {
		return false;
	}
}

Please mark my answer correct and helpful if this works for you

Thanks and Regards 

Sarthak