For me it definitely comes down to a choice between the Red Alert 2 intro, with the live action exposition followed by the CGI invasion of New York to the hell march 2 theme.
Or Dawn of War, where the space marines are fighting the Orks, their predator gets destroyed and the Orks rush only to be stopped last minute by a dreadnought, still has me on the edge of my seat even now.

It's a real shame the rest of Dawn of War's cutscenes are done "in-game", they're nowhere near as good.
Final Fantasy 7 and 8 both get honourable mentions, cracking intro sequences.