System Shock 2
Any Thief game (The Dark Project, The Metal Age) - too bad Looking Glass is out of business...
I'm enjoying Neverwinter Nights, but some people don't like it. YMMV. If you do get it, the official campaign is so-so, but the point is the user-created mods.