Silent Cartographer demo sold the xbox to me, and gaming really as I was only really part time gamer with total war on pc. It's such an amazing level, and at the time it was technically brilliant too. Then there was the level after that! Assault on the Control Room, and let's not forget Halo, Truth and Recon to boot.
Other than that, I know it's against the concept of favourite mission and me having about 10, but I would say you'd struggle to find many really good fps levels outside of Halo, maybe cod - ghillied up was the tits, and CoD 2 is by and far the greatest ww2 game ever, I'd maybe even count Nightfire! Remember that? some of those levels were a lot of fun especially for James Bond games.
My mind is blank, I can't even think of FPS games now, Bad Company 2... the opening was good, but none of the other missions stood out, maybe that desert one...
...and if you tell that to the kids of today, they wont believe you!