Most people agree that pain is unpleasant. What many don't realize is that a thing can be both unpleasant and good.
People in body modification are often accused of pursuing body mod because they "like the pain." While this is true for some, it masks a more important point: we may not enjoy pain, but we don't obsessively shy away from it. In a world of modern anesthetics, where a person can literally go a lifetime without feeling pain greater than a papercut, we choose to undergo painful procedures of our own free will. This is not because we are all masochists, but because we realize that ocasionally experiencing pain makes us stronger individuals. Every time we are tattooed, pierced, cut, or branded, we are making a conscious decision to withstand a (relatively) short period of pain for some larger benefit.
That said, no great tolerance to pain is required to engage in body modification. The pain itself is relatively minor and short-lived, and anyone can withstand it.
What makes the pain of body modification special is not its magnitude, but that it is consciously chosen and usually seen as "part of the process." It is an acceptance that pain is a part of life.
Another important aspect of pain to note is that, in most areas of the West, mechanical and chemical methods of blocking pain are denied to practitioners of body modification. There are good reasons for this, as most anesthetics require extensive training to use effectively and safely. However, because of this restriction, many of those involved in body mod have developed highly effective methods of pain management without external means.
While all of the above is true for the vast majority of those practicing body modification, there is a sizeable and important minority (especially those approaching body mod from a BDSM standpoint) who just plain enjoy the pain.