Well, the good thing about a great story is, the plot itself doesn't really matter. The plot gets rehashed millions of times, and has since the dawn of time. Plot is what you're able to describe without involving any characterization. Plots for a lot of great games are very similar, as was mentioned in this thread about a number of games. Namely, you're the chosen one, or the fish out of water, and suddenly you have to save the shop/neighborhood/world/universe. That tells you absolutely nothing about the story though. The story is about the characters, their growth (or lack thereof), their decisions and reactions that move the plot forward.
Plot is basically what happens, regardless of your characters: you're a doctor who has to stop a vaccine from going to market because it turns everyone into rage monkeys who only listen to pop music and kill people for wearing sandals.
Story is what happens when you put a particular character in that situation: you're a grizzled old doctor, a reclusive expert who'd rather get sent case files than deal with actual patients. Then one day, a case so bizarre comes across your desk you have no choice but to see the patient. You grow attached to the patient as they turn into a rage monkey, and start to become attached again to humanity as you race to outsmart Rage Monkey Co.
That's the story. That's why Jade's adventure in a very simple plot in Beyond Good and Evil makes it worthwhile. You have interesting characters in interesting places, and the plot is just an excuse to escape into an adventure using them as your vicarious avatar for a while. It's not a masterpiece -- more like an exceptionally well done Saturday morning cartoon -- but the ability to tell a story, to even have a good story, is still a developing area of games.
Sanitarium is a mind screw on the level of Philip K. Dick.
King's Quest VII is like an adventure thought up by the guy who wrote Alice in Wonderland
Syberia is...difficult to explain easily. It's like a watered down version of the serial adventures that Indiana Jones was based off of, but still manages to be fun.