Go into your address book, highlight the contact you want to add the ring tone, Blackberry button, edit, Blackberry button, "Add Custom Ring Tone". This will cover all calls and text messages from that contact.
I seem to recall hearing of an app that could possibly do this, but cannot recall the name.
That app is SmartAlerts and works well for me. The other way to do it would be to create exceptions in Profiles for those you want different sms tones. I found SmartAlerts better for me and I like the custom LED for contacts with BerryBuzz. They work well together.
Posted from my CrackBerry at wapforums.crackberry.com