I love a nice ambient drum sound. I can listen to Blakey or Elvin all day long - or Colter's example of Bonzo, hard hitter with a great sound - big tubs tuned up high w/overheads. There are also lots of great contemporary examples of well recorded drum sounds, if you explore a bit.
Having said that, I'm no drum snob. I love my old Slingys, vintage Zildjian A's and Ks, but I also love my drum machine, or the sound of muffled drums, children's kits, houseware percussion sampled and tweaked, etc. all that crap. It all has it's place. Hell, I mentioned the "mashed potato" sound earlier in this thread. I love that sound for certain types of music. It's not the tool, but how you use it.
When I think of terrible drum sounds though, it's bad because it's improperly mixed, or inappropraite for the music,or the band is just plain terrible (can't polish a turd)...