I'd have to say the Wizardry series holds the crown for best story. I, personally, favor Wizardry 7.
How many games out there have multiple beginnings, multiple endings, allow you to actually talk to NPCs instead of just picking between a handful of choices and even do quests or start events in whatever order you want? The Wizardry series was the closest thing I've seen to a sandbox game that actually lets you build your own story as you play. You could even import save games from the previous game to have your starting point based on actions from the previous game. Unlike certain games that let you make conversation choices, the Wizardry games let you make real choices that had real and even sweeping impacts on the overall story. As for the story itself, it's rather crazy. It can be rather cliche at times but so many parts of it are just way out there. For a game, the story was rather amazing even if it had been completely linear. I'd try to summarize it but since it has several variations it's difficult to summarize and I'd probably have to spoil it a lot to even try. If you've never played it, give it a try. Though, I'll warn you that even the most recent (Wizardry 8) is rather dated now. Also, I'm not sure I can call Wizardry 8 a true example of what made the Wizardry series great since a lot of it was simplified and made more linear.
I can't name a single game in the past 10 years that allows anywhere near that much flexibility with the story and I've played pretty much every game in the past 10 years that's worthy of the tag RPG. I'd have to say the only modern games that even come remotely close are the Elder Scrolls games but even those games don't allow any real flexibility with the main plot.
Most sandbox games have really no story at all (I suppose you could argue you're building your own story in those games but you've got no pre-existing story events to really build on). Most (new) games with a decently written story are about as linear as you can get. Some allow a small amount of flexibility and some even create the illusion of being flexible but they're all very obviously on rails. It's very much an all roads lead to Rome situation in nearly every game made in the past decade. No matter which path you take, you're petty much getting the exact same story.
The Wizardry series has remained the bar by which I measure games even today. At least, when it comes to story and freedom of choice within a story. Though, the Wizardry series also had a certain level of detail that most newer games lack.