If you just want to change the sound not do something funky like assign different tones to contacts. You can also create yoir own custom sounds and activate them in Winterboard. If you do it's a little more involved just look in the other JB forum near the top there is a create your own theme ghread follow the links. You will learn how to make your own theme but you can also if you have the sound file also set it up as a theme. If this sounds too involved then like Alli said there's an app for that
I'm using the app "MyProfiles" and that let me change my email alert sound. There is another thread about some people having problems with the app, but I've had it for about a week and it's been working perfectly for me. It really lets you customize a lot of your notifications and create new ones.
Coming from a BlackBerry, I was having withdrawal problems without my custom alerts/profiles. "MyProfiles" has completely solved that for me. Now if only there was a blinking LED notification...