This distinction has been brought up many times by many people, though not as much in reference to gaming, and not in so many words. The gist of this distinction is historical: Epic protagonists from ancient sagas like Gilgamesh and Beowulf do not undergo the same kind of psychological change and examination that modern ones do. Gilgamesh is what he is, and his story does not examine or care about the choices he makes nearly as much as their consequences. A lot of creation myths and parables also fit this pattern. When the character is well-defined and consequences are interesting, it can make for a decent story.
The more interesting story in my mind is the one that examines the protagonist’s choices and their motivation. Consequences figure into their psychology, and their future choices, but aren’t the focus of the story. I want to know who this protagonist is, what makes him great, and why he has decided to use his talents the way he does. If Gilgamesh is a good example of an Epic protagonist, then Gregory House might be a good example of a modern one. In the show, his actions are largely secondary to the motivations he has for them. Other characters spend the majority of the show analyzing and overanalyzing his motives, emotions and affect.
So how does this relate to RPGs? Well, many traditional systems create epic protagonists. Take D&D! The characters created for a D&D game are essentially static, defined by their abilities. Adventures do not force the characters into making tough choices, nor does it examine the (trivial) choices they do make. Playing D&D, one is there merely to see how things will roll out. The orc-slaying ranger is not expected to explain his frankly genocidal tendencies, nor is there any meaningful way for him to explore that bigotry. Like Gilgamesh, he is what he is. Sometimes you can get a decent story out of that.
So let’s talk about heroic protagonists in RPGs. The poster child for this kind of protagonist is found in 3:16 Carnage amongst the Stars, where the meat of play explores the exactly the reasons for the insane genocidal bigotry committed by the characters. The killing-aliens angle is in fact totally irrelevant in play, replaced by flashbacks that reveal the character’s motivations for joining up with the space marines. This psychological examination of character and power is far more interesting to me, and when designing games I hope to bring that out in play. This is, I think, a big part of Being Awesome. Seeing the machinations that produce your Awesomeness make them all the more Awesome for it.
Being a modern person, I am biased toward the psychological, heroic protagonist. But there really is something to be said for crushing a thousand soldiers for getting in your way, for defeating the golden bull, for slaying the dragon. The ideal game, I think, takes both of these approaches into consideration and chooses the right balance for its genre and message. After all, a House, MD RPG, where all the players just analyzed some other player for points would be just as tedious as a game where all you did was kill guys and take their stuff.