I've played too many games to remember the intros, so I think i'm just going to post a trailer.
I really liked dishonored's trailer: https://www.youtube.com/watch?v=zcSc4uBc3nQ
If not for the Drunken Whaler audio, I probably wouldn't have watched it. I think I accidentally clicked on it in steam, and it started playing. The sound really captivated me at that point. Before that, I was thinking how 'dishonored' and vengeance was such a cliche theme, hah.
Damn that was good.
I've never played the LoK series, though after watching that I may have to rectify that fact.
Also I'm not sure if it's been mentioned yet, but the Fallout 3 'I don't want to set the world on fire' trailer deserves a mention.
Originally going to be an episode on Steven Spielberg's "Amazing Stories" TV series, someone brought him back down to Earth when they told him how expensive the special effects would be. He sold the idea to Lucas Arts and they turned it into a game. Considering what year this came out, the 7 minute-long intro of "The Dig" was amazing, visually.
https://www.youtube.com/watch?v=EGzX_7ZN8_U
It has lost some of its punch now that I've seen it a hundred times, but Skyrim's intro sequence was great. I especially liked how it gave you a smooth introduction to the core elements of the game's story: you get a short briefing on the major players in the civil war as you are carted in to Helgen, you get a handle on what/where Skyrim is for those who don't have previous knowledge of Elder Scrolls lore, and you're (violently) introduced to Alduin.
I would also second the World of Warcraft intro. The cool thing about the original intro/trailer is that it didn't focus on the big heroes of Warcraft lore, just generic archetypes. That's perfect for an MMO, since your character in WoW isn't one of the lore heroes, they just follow a hero archetype. So plenty of people saw the trailer and were instantly inspired to roll a dwarf hunter or a Night Elf druid or whatever.
The cinematic trailer for https://www.youtube.com/watch?v=lZqrG1bdGtg is impressive. The https://www.youtube.com/watch?v=D6h987UqYr4 trailer offers a more light-hearted view of zombie apocalypses.