Folks have already mentioned Monkey Island and Grim Fandango, which I definitely wave the flag for.
But for me the ultimate game theme will always be the Leisure Suit Larry theme. It may not be the most beautiful tune ever to come from a computer game, but dammit, it's the one song that I can still hum note-for-note 20 years after I first heard and played it.
The Space Quest series Roger Wilco theme comes in at a close second, but this time for more musical reasons - it's a pretty brilliant pastiche of certain sci-fi theme songs from the 70's. I can hum that one
almost note for note to this day
