Try Profile (by noimjosh). I use this to automatically switch settings based on time. I haven't tried it myself, but it does have options to set SMS/MMS volumes separately from other notifications.
Edit:
I changed one of my profiles so the SMS volume was separate from normal notifications, and it worked. In all, Profile can control the following:
- Ringer volume
- Ringtone
- SMS/MMS (each can be tied to Notification volume, Ringer volume, or their own SMS/MMS volume)
- Notification volume
- Notification ringtone
- Music volume
- Alarm volume
- In call volume
- Screen brightness
- Screen Auto-off timeout
- Turn WiFi On/Off (or unchanged)
- Turn Bluetooth On/Off (or unchanged)
- Turn Airplane Mode On/Off (or unchanged)
You can create as many different profiles as you want, and pick which of the above settings you want to control. It can also be made accessible for the notification bar for quick switching, and it can also be set to automatically change to a profile at a set time.
It also comes with 3 different widgets. One which can be tied to a specific profile you've created, one called Current that pulls up the current settings where you could temporarily change a single setting, and one which will pull up a list of all profiles.