Dawn of War. Dawn of War 2's one is good too, but doesn't come close to Space Marine vs Orks. I would've named Bioshock, but the actual intro isn't as good as the ingame intro. Welcome...to RRRRAPTURE!!! Combined with that music....shivers, it has the best ingame intro I've ever seen. Dawn of War gets my vote however, for the best prerendered intro.
Edit: O wait! No what am I talking about, Star Wars The Old Republic gets the prize for best intro, since I can't beleive that they will not use this epic trailer as their intro:
Those Jedi...those Sith....that Bounty Hunter....oo gods I just threw up from all the awesomeness in that trailer. That must become TOR's intro.