If it matters any I have an HTC Desire Z with the original Sense install.
We have this monitoring service at work that keeps an eye on the various servers and starts frantically SMS'ing me whenever things aren't working right.
I'd like to set a separate notification tone for SMS's from that one contact. How do you do that? Under the contact I have an option for the Ringtone, but there doesn't seem to be an option for the SMS notification tone.