The opening of Metal Gear Solid 2: Sons of Liberty (PS2) stands out to me.
It has a very cinematic opening, reminiscent of a movie trailer, with music by Harry Gregson-Williams. As a whole Metal Gear Solid 2 didn't strike me right, I found it's story annoying and the environments very bland with occasional moments of frustration. But the opening was spectacular, sadly kind of misleading about the game.
Runner up would be Total Annihilation (PC). Another great orchestral score, Total Annihilation was full of them, but I really the intro with the music.
Metal Gear Solid 2 and Total Annihilation intros, respectively.
http://www.youtube.com/watch?v=ifPZsRgJIXE
http://www.youtube.com/watch?v=iSB-Rk1sYZw