The opening cinematics from Starcraft, Diablo II, Warcraft II and Warcraft III [with their respective expansions] all come to mind. Pretty much any Blizzard cinematic, really. Which i'm sure we can all agree upon.
Relic's work has also been quite good as of late. The animatics from Homeworld2 were quite excellent. Although Homeworld1's have most certainly not aged very well at all. Further, the opening sequence for Dawn of War would without a doubt be one of the most intese opening sequences i've ever seen in a game. The only downside is that it was the only goddamned cinematic in the entire goddamned game!
The Star- and Freelancer opening sequences also come to mind, especially the latter. Nothing quite beats seeing those Coalition Carriers coming appearing in Europa's orbit and opening up with those excellent looking & sounding cannons mounted on the bow. A great moment in sci-fi oriented gaming.
Fable isn't all that bad, either. The slow montage of footage from the Great Hall with the nice music is most definintely a great way to start the game.
The Battlefield series, most notably Battlefield 1942, also have excellent opening cinematics. The '42 one might not look as spectacular nowadays, but back when it first came out, it was unbe-****ing-lievable.
Dungeon Siege II also had a rather fun opening sequence. Not all that great a game, but those first few minutes were quite excellent.
Fahrenheit, which I just bought yesterday, must also get an honourable mention. It's not a flashy intro, in fact the graphics are rather substandard for the time in areas. But it's the artistic integrity of the sequence that just makes it so awesome. The slow zoom on Lucas covered by the shadows of moving clouds, all on the expanding band of footage with a background of falling petals just looks plain beautiful any way you look at it.
And finally, let's not forget Freespace and Freespace 2. While the Battle of Deneb is unquestionably awesome, i'm going to have to say the opening for Freespace 1 is the better of the two.
