I have an X1900XT from Sapphire and the only time it's unusually loud is at bootup. For 3-4 seconds, the fan will while like a jet engine but then quickly quiet down. It's a little louder than my old 7800GTX 256mb card, but not to the point where I care.
Keep in mind that I also run a lot of case fans -- 7x80mm to be exact. But even at 4x80mm, the card was hardly noticeable. It's still quieter than my Xbox 360, even with all the fans at full speed.
If you're looking for an aftermarket cooler, I've heard that the Accelero X2 is your best bet. It might be able to cool your card by up to 15C, depending on your case. More importantly, it's whisper-quiet. Just remember that it will probably void your warranty (I've heard of some companies that will still uphold the warranty, but very rarely).