It's possible. My friend and I were browsing through my iphone files and figured out how to swap sounds...Here's a tut:
Open up WinSCP or Iphone Browser...(I use SCP). Navigate over to System>>Library>>Audio>>UISounds. There will be a bunch of sound files in here, so look for the ones called, "sms-received[#].caf"
You're going to replace one of those files. Each one corresponds to the ring tone under the SMS division in settings.
Just take any song you want, crop it to around the same length of the file you want to replace, and convert it to a .caf with this program.
Protected Music Converter - convert DRM protected music (WMA, M4P, etc.)
Once done, put the file in there under the SAME NAME as the one you replaced, go to settings and click it, and you're done. Hope this helps.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
the above converter doesn't covert to .caf....I'm still looking for one that'll convert to .caf, but until then, you'll have to google it yourself.