For me it all depends on context. There are lots of RPGs, especially WRPGs, where you are obviously working to save the world from imminent doom and EVERYONE knows that, but then you get sucked into a 10 hour side arc about settling a personal dispute between two groups that are bitching with each other over something stupid, or you have to do 10 different people's lame ass chores. When that happens I'm always like "I'm saving the fucking world here, we're all on a countdown timer, and you know it, GTFO my way" so when the main character does that too, it's extremely satisfying. Sometimes I even want my character to be an extra dick just to drive the point home, like playing renegade in ME2.
Of course, the opposite can be true as well, where you're just one dude interjecting themselves into the lives of others, so they have no reason to give a crap about whatever your deal is, and then you're a jerk to them. That's not cool. Then it's much worse when you have a small team and your character doesn't care about any of the people who are being his sole friends in the world.
That's actually one thing I appreciate about games like Fallout. Your character is just the choices you make. Yes, it's a bit of a narrative hit, but you aren't forced into acting out someone else's notion of the adventure. If someone betrays you and are cocky about it, you can shoot them. Yeah, you potentially lose missions related to them, but that is your choice in the postapocalyptic setting. Do you want to be the guy people think they can fuck with? Do you want to be the hero? You can negotiate or not, be gracious and helpful or not, it's your call.
Something like TEW2 seems to strike a middleground by having less clear circumstantial morality or scope. It's just a story of people with their own drives and desires in the situation. It comes off more natural/intimate/real that way, and in some ways is a relief from games that have you bearing the weight of the world. Higher ethic doesn't always need to be in the picture for game narratives.