It wholly depends on the game.
To the extremes, if I play something like Final Fantasy VI (my favorite game of all time) it absolutely must have a good story and characters but with something like Tetris (my second favorite game of all time) there's no point in adding a story and adding a story only takes away from it (Tetris Worlds).
Saying all games require a good story is just stupid because not all games require story. Gears of War would not be any better if it had a fantastic story because that game is all about the action. The story in Uncharted is completely derivative with a terrible ending (in both games!) but they are both fantastic games. Mario games have been using the same story for the past 25 years and nobody (that matters) complains about that. Those games don't rely on strong story because they rely more on other things.
On the other end, a game like Final Fantasty absolutely must have a good story because that's simply what the game is all about.
