All time favorite:
Half-Life- In 1998 the standard to beginning a game was a short interlaced cutscene, embedded after the logos. When you started a new game you were dropped right into the action with the story already unfolded.
But when I booted that black CD, there was no intro movie. There was no substandard gun or melee weapon already in my hand. It wasn't until ten minutes or so later until you see someone die or brandish a weapon.
The game started off, with you as a normal man on a train. You were just going to work and taking in the breathtaking vista you are provided. Your route and vantage point showed you next to everything you were going to face within the facility and an early glimpse at your antagonist. It demonstrated the pinnacle of game technology back in 1998.
Honorable mentions:
Fallout 3- You enter this world from your birth. You see yourself grow from infant to adulthood and have your relationships sink in. I always felt that this attachment and storytelling wasn't as done as well in the rest of the game. If it was, it might have become my favorite game.
Call of Duty 4: Getting manhandled and abused while enjoying a car ride where you take in the view of the country you head being brought down. Then you are executed. All in first person!
Fable 2- You're an urchin taking odd jobs with your crass but loving sister, before she is torn away from you. I didn't like Fable 2, but I loved the childhood part.
Starcraft: Brood War- A masterfully shot scene. It starts off on lava drenched trenches where a visceral battle is raging. We feel the fear, sweat, and desperation in a lone Terran marine as he tries to fend off an oncoming Zerg swarm. He is saved, but not for long as up above their leaders coldly watches raging consumption of lives as if it were a game of golf before abandoning them.
Diablo II- Even after 12 years, it still looks beautiful. It wasn't just looks either, the audience is engaged in great storytelling by being introduced to two mysterious strangers.
Dark Colony- I was in the third or second grade when I played this obscure real time strategy game. The level of carnage I witnessed in the cutscenes of this game was unprecedented from any other.
Dark Colony Intro: http://www.youtube.com/watch?v=3ByIYt_Wp4I