Personally, I loved Fallout 1's intro, it is so amazingly simple yet it is such a prolific portrayal of the world it takes place in.
I also loved both Dawn of War intros, they're very nice at bringing the grim gothic future to life.
Quake 2's intro I still love to this day since I saw it so many years ago in primary school. It was my suspensive aquainting with the strategy of drop pods...
Legacy of Kain: Soul Reaver I also love, the voices of the characters, their personalities and their dialog inspire me to my darkest corners.
Neverwinter Nights 1 & 2 both have awesome intro scenes.
Deus Ex I see the intro and get the idea of the world, yet the suspect that there is far more than I know behind the veils.
Blood 2: The Chosen's intro portrays Caleb as the badass he is while explaining the previous game's base plot, I love its style.
UFO: Aftermath's intro sends a shiver down my spine, both with its visuals and brief explanation.
X-Com: UFO Defense (AKA UFO: Enemy Unknown) is just awesomely stylish.
StarCraft and StarCraft: Broodwar both had intro scenes I thought were awesome. Upon playing through the campaigns I was treated to many other awesome scenes.
Crusader: No Remorse and No Regret both have introductions that I feel are quite badass.
Borderlands' intro I find quite amusing and stylish.
Half-Life's introduction, while not a video, I find rather well done, it really builds on the idea of a normal work down going down hill...
Mass Effect's intro, while small, I always do enjoy. Many of the game's scenes are awesome to be a part of, so to speak...
BioShock's intro I find rather delightful, a wonderful way to make a bad day worse...
Dragon Age: Origin's prologue stories I find to be great introductions into the game, very nicely setting up just how you're seen in the world rather than leaving all that up to you.
Vampire: the Masquerade - Bloodlines' intro I find to be a grand way to be thrown into the world of darkness. But then again, maybe I just like the idea of vampires in bed...
If you manage to trawl through my excessive list, I commend you. I also apologize for my laziness in not finding videos of these for those who're interested.