I'll have to throw a few out here.
MSG1... that's right, JUST ONE. Kojima had a good story going, then ruined it with a sequel dealing with gay vampires and noob commandos with whiney girlfriends. The first game was the only one where I actually gave a crap about the villains... the rest of them couldn't die fast enough.
KOTOR... not the second one, which is a shame. It would've been a wonderful story if they had finished it (I'm aware it wasn't Bioware). Mass Effect, while engaging to an extent, didn't have nearly the story or twists of KOTOR. I consider Mass Effect to be a BioWare game for dummies... I'm hoping the second game steps it up. I can't wait for Dragon Age: Origins though.
I'm surprised no one's mentioned Chrono Trigger yet. Not only do they give a lot of character background for everyone, but there's a point in the game where you can't play the MC. I won't spoil it, but that part of the game blew me away, and I'm surprised it hasn't been tried again.
Out Of This World. This one gets even more props because it tells a grand interesting story, and doesn't have a shred of dialogue in it: no voice, no text, nothing. This is an old game people should try if they haven't... you won't be disappointed.
Star Control 2. I still hate how the developers crapped all over this series with the third installment, but the second game was a real gem. It was spread over a huge chunk of the galaxy, there were stories going in that were central to the plot as well as completely independent ones, you had a time limit, and it marched you forward at a decent clip that never felt rushed. Try this game at least once... you can download it for free.