Oh Christ, too many to name. I'll second, third, fourth, tenth all the Final Fantasies; CGI or sprites, it doesn't matter. FFX , FFVII, and yes, FFVIII (beautiful) stand out. Onimusha 3, though I never played the game, the intro was fantastic. Diablo 2 comes to mind. Chrono Trigger! God, Square really did great fucking sprite intros back in the day! Chrono Chross! Splinter Cell! Super Metroid! Alice! The first Panzer Dragoon! I'll agree with everyone on all these accounts.
My absolute favorite would have to be both intros to FFVI (the new CGI one and the classic scrolling one). The theme is fantastic in the original sprite one and just from a few bars of midi music, you just FEEL a long, long epic story about to begin. It reminds me a LOT of the opening crawl of Star Wars, only with scrolling shots of the world. The "new" CGI one was perfect for nostalgic reasons: Terra/Tina looking up as the first snow flakes fall; her slow, escorted march to her Magitech armor; Celes in chains, looking up as Locke walks into the room; Celes in her opera dress overlooking her suicide jump, her dress slowly fades away and we see her in battle costume; the opera scene; Shadow killing ghosts on the ghost train (a quick shot of Relm that many didn't see); Celes with Locke's bandanna; Setzer going "Gambit" on the machines chasing his airship. Just. Fucking. Classic.