Also check out MP3 ringtone maker, dowload a song and edit it to an appropriate size for a notification, SMS, ringtone, alarm tone.
Also if you want to assign a SMS tone to a particular contact I recommend HANCENT SMS which IMHO is much better than the stock messaging app (loads of customization).