UT 2003
Red Alert 2
Titan Quest
Age of Empires 2 (link is to the FULL uncut version of the intro. Now the trebuchet destroying scene actually makes sense.)
Dawn of War 1
Dawn of War 2

EDIT:
Remembered a few more which I like a lot:

Heroes of Annihilated Empires
Armies of Exigo (I love the helmet design)
Hellgate: London

I have a few. Diablo II does stand out, as does the first Witcher game.

I don't have a single favorite game intro, but here are some that I really like, from older to newer:

Maniac Mansion.
Zak McKracken.
Another World.
Civilization.
Legend of Kyrandia
Diablo 2.
Marvel: Ultimate Alliance.
Darkest Dungeon.

Edit: Added a couple of games to the list.

My favourite would be Fallout 1 and Fallout 2 intros.
But the best intro in games IMHO is Half Life.

Not a huge fan of intros. I prefer it when games tell their story through gameplay instead of clips or text or such. Games are a unique storytelling medium and should play to their strengths. If I want to read, I'll pick up a book. If I want to watch a show, I'll put one on and sit back.

Heathen! Heretic! You of all people should have mentioned Theme Hospital!

No, really. That is an hilarious intro for a very funny game.

Almost forgot how good that intro was. It introduced the player to the world and set the tone for the adventure perfectly. Two minutes in and you're fully invested in the story, it's brilliant.

"From where you're kneeling it must seem like an 18-karat run of bad luck. Truth is...the game was rigged from the start."


BOOM! what a way to start a mystery revenge plot!

Exactly! And books should never contain pictures, and videos (film & television included) should never contain text -- not even subtitles! Gotta keep 'em segregated. Down with mixed media!
◔_◔

I guess I'd have to go with the Dawn of War intro. Back when the game was released I watched this intro like a million times. It has lost a lot of its magic over the years but it still holds up very well (excluding the terrible audio mix) and back in 2004 it was just mindblowingly good and made probably any Warhammer fan wish for an entire movie in this style.

And as many complaints as I have about Brood War, its intro is probably my favourite cutscene in Blizzard's entire history. The CGI has aged quite badly but goddamn, in terms of atmosphere and editing it's still quite amazing to me.

And since I'm a huge Amiga fan Cannon Fodder's intro will always have a very special place in my heart. It's of course mostly thanks to the music but there's also something hugely endearing about how the developers introduce themselves there.

Back in 1997, the intro to Oddworld: Abe's Oddysee really stood out. 20 years later, it's still surprisingly good.
https://www.youtube.com/watch?v=249vWCD2neQ

Oddworld: Munch's Oddysee also had a great intro, it was so good it put the rest of the mediocre game to shame.
https://www.youtube.com/watch?v=wjLmtGKQtEw

You missed my point. Games are multimedia, so of course they will have all those things. My preference is to have more gameplay and less passive downtime not actually playing the game. Does that really sound unreasonable?

I got your point. But it's a strange point.
Your "gameplay first" preference is your preference; nothing wrong with having those. But asking that games contain NO "passive" sections -- no matter how brief (and a lot of intro videos are no longer than two or three minutes) -- is going just a smidge too far. Don't like cutscenes? Skip them. (Or, better yet, skip games that you know rely heavily on them). Don't like long in-game texts (such as books in Elder Scrolls games)? Ignore them where possible. (If it's not possible, that's not the game for you; you'll probably have to ignore most RPGs, apart from maybe those featuring dialogue wheels with short but hilariously misrepresentative summary text for your response options).
I can't imagine how one would go about making any kind of deep RPG without having a fair bit of in-game text, even if all of the exposition and character dialogues were fully voiced. It's gonna come off as a fairly shallow (or illiterate) game world if there's nothing within it that you can pick up and read. I think cutscenes are generally a lot less necessary, but I'm also certain that there are plenty of people who would disagree with me there.

At any rate, not every game is a mystery. Not every game would be best served by just dumping the player into the game world and making them figure out what's going on from environmental cues or whatever, despite the fact that their character (unless they're amnesiac, or have just arrived in an utterly unknown land) would have no reason to be so clueless. And while having a good bit of backstory and lore printed in a manual (along with crucial info about the gameplay controls and interface, etc.) is nice, most people (especially now) don't read manuals. Having a brief encapsulation of "this is the world, this is your character" in video form (or voiceover, or even text, if the game is old or its budget was small) is no terrible thing -- particularly if it's at the very beginning of the game, which is what this thread is about.

See, I feel you already read more into my message then was there. I never said there should be none, even though I prefer to have less.
And yes, I try not to play cinematic games. I don't generally read walls-of-text in a game. I try to stick to things I like.
I was not trying to dictate what every game should be. I do think more games should try to tell the story in ways that only games can, but there is no one size fits all in this.
I saw this thread and gave my opinion on intros: because I generally feel sitting through an intro is a chore, for me less is more. It's just how I am. I think you may have assumed some antagonism on my part that wasn't there.
Are we cool?

I always liked the intro to Day of the Tentacle... oh, and the tram ride at the beginning of Half-Life.