Anecdotal only and obviously it would be a case by case basis, with each officer and location going about it differently, but I've never had anything but good experiences with resource officers, from either the student side or the school employee side. It's definitely a job that requires a certain attitude that might not be 100% common in police officers. But even with my normal nervousness around police, I got used to the presence of one always at the school.
They got a lot of kids who might not see police as a positive figure seeing them on a daily basis as non-threatening, if perhaps even pleasant sometimes. When needed it was an extra step above school punishment. A kid might sneer at a teacher, a dean or even a principal, but that uniform is a whole other level. Not 100% effective, but often it's what a kid needs to see logic. A scare tactic, yes, but one that might keep the kid out of worse trouble in the future.
Can't speak for all, but I've interacted personally(beyond simply seeing them in the halls, it'd be a dozen or more if you counted those) with 4 different resource officers throughout my life. And it was never a bad experience. They all seemed to be disposed to dealing well with kids. And I can't even say they were all great people. (One had a divorce/affair situation that was messy enough it made it to the local news.) But in school they were professional, they cared about the kids, and if it ever came to "pulling rank" you knew it was because it was a serious situation and they were doing what they could to keep the kids out of trouble rather than simply be there as a guard or as some sort of muscle.
With that said, there was, in the last few months, a local news story of a resource officer a county or two over found out to have been molesting kids. So obviously there are bad ones out there. But that's true of any school official.