You need to download a different program for that. Some people like Handcent, some like GO Messaging. I prefer Glowfly since it's simple and does what I need.
I haven't used Handcent in quite some time but the feature definitely used to be there. Handcent has so many settings it's easy to miss what you're looking for...
I don't know of a way to do it unless you've already composed a text to them or they have sent one to you. When you bring up the thread, in the upper right corner, you will see the settings button (looks like a person with a cog wheel). Click this and then Notification Settings on the pop-up. Then "Notification sound". From there you can assign that person a custom notification sound.