The app you are using has to have the option to change the sound in the settings or it will just use the system default sound. I use Message+ and it allows me to change the sound.
Posted via the Android Central App
Maybe I read the original post incorrectly but I think he's asking if he can use different sounds to differentiate MMS from SMS. I'm not sure if any application does this but I'm just throwing this out there in case someone knows.