If you have Phone Disk installed, you can go into
Go to /System/Library/Audio/UISounds
Here you can change one or all of the default sms tones.
sms-received1.caf -> Tri-tone
sms-received2.caf -> Chime
sms-received3.caf -> Glass
sms-received4.caf -> Horn
sms-received5.caf -> Bell
sms-received6.caf -> Electronic
You can also log in through SSH as well and change it in there as well, but PhoneDisk works good.
Ive got the Wii Forecast Channel as my sms received tone. You need to convert it into a .caf file first. Cant remember how I did it....it was 18 months ago, but theres plenty of guides out there.