Bioshock's connections to the novel are shallow, at best.

Oh! Megaman Legends 2. The intro is fifteen minutes long and all done in-engine. It's like watching a polygonal Megaman anime. It's quite decent considering how old the game is..

I put mine in my handbag for self-defense. Works like a charm!

World of Warcraft
World of Warcraft The Burning Crusade
World of Warcraft Wrath of the Lich King
Left 4 Dead
Alpha Centauri
Crysis
Must have a subwoofer for the best effect.

Like others:
Dawn of War
Metal Gear Solid 2
Beyond Good and Evil
Grim Fandango/DOTT/Sam and Max
But I also found the Battlefield 1942 Intro to be really awesome, got me pumped for the game.

While it's not exactly an intro per say, considering how it is only halfway released, it is really good. The Freelancer extended intro is not only as epic and good looking as the short version, it also goes above and beyond that with some of the- quite frankly best 3D computer animation I've ever seen.
http://www.youtube.com/watch?v=IicV3p__lEc
The planets... they go *poof*!
Poof! D:

Whilst probably not my favorite intro, in terms of how long it has stuck in my head, as well as going back to watch it again and thinking how awesome it was, having gained a greater understanding of the context in which the intro is told, a great intro for me would be Wizardry 8: http://www.youtube.com/watch?v=ZFTP6DnL7Z4
I still get a huge urge to play this game when I hear:'who will prove worthy, who will become cosmic lords themselves?'
(Admittedly, it means very little to those who have never played Wizardry before (like me before I played Wizardry 8).)
Also, for your continued entertainment, somebody mentioned the Cannon Fodder intro, well here is the promo music video the devs made around the time of the games release: http://www.youtube.com/watch?v=hDxCBx9Xb1I
Now if you will excuse me, I am off to ascension peak.
Oh, and the intro to Myst V: http://www.youtube.com/watch?v=xEXlxcog_CM I guess I adhere to the belief that the simplest is often the best. That, and it is a painfully sad sequence.

In agreement with rob.liefeld, DarthJDG, and JadeUzuki.
The Curse of Monkey Island ( Monkey Island 3 ) - Watch It
It is funny, well animated, and has great music, but it does help to know the previous game storyline to fully enjoy it.

Another good one is the intro for Supreme Commander, not FA mind you (how dare they kill off the only cool character in the whole game in the bloody intro?! I practically bought the game on the premise that there'd be more Clarke. :'( )
No, the vanilla intro is delightfully epic. The narration is suitably overplayed and the excellent score compounds the grandeur. Sometimes I fire up the game just to watch it again.
http://www.youtube.com/watch?v=lgpOfVsw62g