Personally, I happen to like closed cell foam pads. My pillow doesn't fall off the pad immediately like it does with inflatables, and I don't slide off halfway through the night, either. Firm is actually more comfortable for me.
The main reason I can think of why a rolled ccf pad is bad is because it's about 20 times bulkier than the inflatable alternatives. I don't mind this usually because I just strap it to the outside of my pack, but at some point, it does get to be limiting, of how easily it is to access the inside of the pack, of how full you can stuff your backpack, and of what else you can strap to the outside of your pack.
Another problem is that you just aren't going to get the kind of warmth out of a closed cell foam pad as you can get out of something like a good 4-season inflatable. I've used both kinds and short of bringing so many ccf pads to stack that they are too big to carry, it just doesn't equal the warmth that a good 4-season inflatable can deliver for less bulk and comparable weight.
In my experience, leaks in inflatable pads are rare, easy to mend in the field, and with moderate care, they just don't happen. If you're worried, just get one made out of a more durable material.