There is one game (well, game series) that stands as the single most glowing example of this, towering above all others; and that's Tecmo's Deception series.
So, here we have the single most brilliant concept for a game idea ever. You've got an evil castle filled with evil traps and evil minions, and as a surprise twist, it is YOU that is the great overlord of it all, defending your dark realm against the stalwart heroes of the land.
The idea of playing Castlevania and you being the extremely powerful last boss (err... disregarding Metroidvania titles...) is extremely appealing. I think even the sweetest of goody two-shoes people have a part of them that wants to rule the bad guys and have them do your bidding against the pitiful heroes that would face you.
Unfortunately, Deception falls flat on its face in virtually every aspect of its execution. The idea of ruling an evil castle is cool IN THEORY, but rather than being an all-powerful super-entity with hordes of minions at your command, you're a faceless, defenseless, frail twit that spends his time playing Marco Polo with his enemies in the hopes that they'll chase you into one of the little traps you came up with in the tiny sim segments.
The first person perspective kills any empathy you'd have for the character you're playing, and the fact that he's more of a weaselly coward trying to fool idiots into doing something stupid does little to support the feeling of absolute power and corruption you're supposed to get from the game.
What could have been one of the greatest games of the PS1 era turned into a shoddy, boring piece of garbage, noteworthy only for having a special unlockable monster on the original Monster Rancher.
