Mass Effect 1 (2 was a great game, but much weaker on the 'story' front) comes to mind.
Also Halo:CE. You are given very little information and basically thrust into the story, and you are just one peice of a larger story. Though you have a very "lone warrior" feel, you are constantly reminded that thousands of other people are working hard to accomplish the same goals, and are rapidly dying off in the process.
And Bioshock. I still need to finish it.... (Just got a new gaming PC, so I finally will) From a game perspective, it's pretty lousy. The storyline makes the game worthwhile. If you ever play it, blitz it on easy... no sense having a tough playthrough.
And if we can step back in time a bit, I loved the storyline of the Wing Commander series. Particularly through the Kilrathi war...
