I thought the beginning of Golden Eye was great, what with the bunji jumping off the damn and then the escape by riding a motorcycle off a cliff and then free falling into the airplane. Amazing! It was one of the best Bond openings of all time!
That one's definitely up there (and my favorite Bond song too). I also love The Spy Who Loved Me's opening in all its '70s glory.
Jack really hit the nail on the head with Ghost Ship. It's like the intro came from a completely different movie. The House on Haunted Hill (remake) was another one pretty much blew all its scary in the first 10 minutes (although HoHH was at least kind of fun and campy).
Favorite opening credits:
Watchmen
Se7en
Zombieland
The Royal Tenenbaums
Napoleon Dynamite
Gentlemen Broncos (another one where the opening was the best part)