I've found that many of us have been wanting to change our text (sms) sound only to find that it only affects notifications. and our sms stays at default. I've broken it down to 2 parts. alas here is what you do:
1.from the home screen tap on messages, then tap your dedicated menu button. then tap settings. then scroll down to select sound at the bottom of the screen. from there you can select from your various notifications sounds.
now if you don't know how to add your own customized tones to that list. here's how.
2.find whatever sound you desire from the internet or whatever (I wanted that cool "DROID" robot voice thing as mine) then mount your phone to your computer. open the file that says media. then under that select audio. from that you'll see a notifications folder. all you have to is click and drag your music file from your desktop to that notifications folder. then go back to part one and your look for your file in the list.