First, the heat sink is not actually attached to the CPU, but rather simply presses against the CPU (which a bit of thermal paste in between, to reduce the amount of air in the coupling between the CPU die and the heat sink; you might be surprised just how good a thermal insulator non-moving air is). Thus, the CPU itself is under no significantly different physical stress depending on the size or weight of the cooling assembly, including the heat sink and fan, assuming of course that they have been properly installed.
Second, as modern CPUs dissipate potentially quite a lot of power during use, motherboards provide reinforced cooling supports precisely to cater to the potentially large, heavy cooling assemblies for the CPU that a user may wish to install. Also, the cooler itself may very well come with stronger support posts that attach to the motherboard. The specifics of how the motherboard is reinforced varies -- particularly between Intel and AMD, it seems to me -- and the particular ratings for how much weight these reinforcements can take are likely to be different between various motherboards. (This is largely speculation, but I would expect motherboards marketed to the gaming community to be sturdier in this regard, since high-end CPUs and graphics cards need relatively large cooling assemblies to operate for prolonged periods of high load at safe temperatures, particularly with acceptable fan noise levels.) However, possibly with the exception of the lowest-price, low-performance motherboards designed for fanless or near-fanless operation, they should all be sturdy enough to support the weight of even a relatively large CPU cooler, even when the system is installed in an upright configuration.
As a personal anecdote, my own system has a Noctua NH-D14 CPU cooler (which is massive, almost to the point that it didn't fit in my case; it only left centimeters of clearance and made, ironically enough, the CPU fan power header on my particular motherboard very difficult to reach) and one thing I did try after installing it was to rock it gently in various directions. It remained solidly in place.