Yeah, Brutal Legend (and most any other game where you have some sort of heavy metal for bgm works) would be up there, but I would have to admit that the various background musics that play during the Xenosaga games are some of my favorite. They have this sort of haunting beauty to them, its just great stuff.
Along those lines, Dragon Age has one of the best opening songs ever. No lyrics, no guitar solos, just someone singing. And it is awesome.