In short, yes, it's possible to change any sound you want!
It takes a
little bit of know-how to do it right.
There's also a way to make ringtones from your music without having to pay $2!
Search around the forums/Google for a tutorial. Keep us up to date on your progress and/or success/fail story. haha
Oh, btw, I've included what I use for my SMS sound. It's the Star Trek communicator from
Star Trek: The Next Generation.
Easy way to add this is to download iPhone Browser from:
Downloads - iphonebrowser - Google Code
Then drop the file I attached into the "UI Sounds" folder. It will overwrite your first stock SMS tone after a reboot.
Before you do
anything though, read one of the tutorials to get yourself familiar.