I think a source of misunderstanding lies in the fact that when certain traits are considered to be better than others, it doesn't mean that some are great while others are terrible. The spectrum is not that wide - having two bottom-tier traits may be a slight disadvantage, yet can be countered by other factors, such as UUs, UBs, starting land, and/or, of course, good play.
When talking about aggressive and protective specifically, it should first be noted that aggressive is generally considered to be much more useful than the latter, for reasons mentioned in the previous posts. In fact, I wouldn't regard it as a low-tier trait at all. However we evaluate its value, it makes more sense to look at both traits individually and not lump them together.
Protective is arguably the least useful trait - in single player. The case can be made that every trait is potentially useful, as long as you adopt an according playstyle to make use of your traits' potentials. Because of the nature of wars in Civ4, in many cases it is simply suboptimal to modify your playstyle to make use of protective.
However, everything changes in multiplayer, in which I'd rate protective among the top-tier traits. Not only does it help immensely in the early run for city sites and land grab (which is usually the most important part of the game), it also acts as an efficient deterrent throughout the game.