Some famous SID composers:
You can still enjoy tunes by these composers and many others by downloading the HVSC, a collection of more than 20,000 SIDs (2003). You'll need a player (SID emulator) like sidplay2/w which can be found on http://www.d.kth.se/~d93-alo/c64/spw.
The legacy also lives on in terms of C64 remakes; amateur composers (often talented ones) who contribute with their own interpretations of SID classics. remix.kwed.org is a good place to start. I recommend the following artists:
Of course, this can't go without mentioning Press Play On Tape; a rock band playing C64 music on real instruments.
A few commercial bands even include actual SID sound in their work:
They don't actually use a C64 since the audio signal was originally combined with the TV video signal (See also: noise and hum). Instead they have hardware like SidStation or HardSID; both combine modern audio hardware with one or more SID chip(s). Ain't that something? It's like putting an ancient Rolls-Royce engine in a Hyundai.
On a last note for amateur composers: The audio software company reFX (refx.net) used sidplay2 technology to make a virtual C64 synth (a VSTi) called QuadraSID. Also, the music application Goat Tracker features a retro interface, and emulated SID sound.