There are quite a few games with intriguing and well executed stories. Of course, everyone has their personal favs, that's undebatable.
MGS games are basically in a league of their own (with no 4 beeing my fav), and as honorable mentions i'd pick (in no particular order):
Silent Hill 2, FF8, 10, 12 (though a bit too mature for it's own good), Chronicles of Riddick: Escape from Butcher Bay, Assassins Creed (the presentation could have been a lot better), DMC3,4, Uncharted.
"You have the right to the remains of a silent attorney"