I think my favorite one is the Waterloo level in Psychonauts. I really liked the whole idea of becoming part of a board game at different scales. Some other I can think of, in no particular order:
Any of the other Psychonauts levels (they are all totally unique, and unlike any other game out there)
Into the Lion's Den in FS2 (the mission has its flaws, but it was just way cool back then)
High Velocity Weapons Research in Descent 3 (loved those high speed tunnels)
Hell in Painkiller (the final level, a creative twist on the phrase "war is hell")
Fear in Cryostasis (probably the trippiest level in this game, and full of symbolism)
Normandy level in MOH: Allied Assault (very immersive and cinematic for its time, although less impressive today since many games have since copied it)
Alien base mission in Crysis (great atmosphere and artwork, and had a very alien feel to it)
Rachni mission in Mass Effect 3 (similar to the above, but still very good)
Liberty escape mission in Freelancer (as mentioned by someone else)
Thief 3 was an excellent game, but I thought that Shalebridge level was a bit overrated. I guess those types of levels just don't do it for me.
That sound is pretty much imprinted on my soul. *shudder*
Actually, Secret Level 1 was my other favourite.
RRRRRRRRRRGGRHHGHGHHH *great big purple blobs fly at you*
D2 unfortunately replaced both sounds with these really crappy mouse squeaks, although the D1 versions can be added back in.