It is Natural England who have the say so. My experience is it depends entirely who you deal with. Some are sensible, others less so. I get the impression it is a lack of experience/ confidence that results in over-cautious restrictions. Likewise such folk are easily steam-rollered by management when a big event crops up.
You get a very good insight into their job/ the process on the recently repeated
Tales from the National Parks, particularly the Lakes one.
SSSI designation carries no automatic restrictions as such but that is the perception. The full notifications are available on the web, and are usually well worth reading as you could easily scotch such nonsense as nesting Herring Gulls - they would not be likely to be mentioned. In my experience sites notified for botanical interest rarely involve species on the crag itself, and sites notified for geological interest are unlikely to be damaged by climbing. Being pre-armed with this information makes discussions much easier - most NE staff will assume you are totally ignorant of nature.
Most geologists see climbing as positive if anything, keeping the exposure clear. I was a little surprised to hear the relevant geologist couldn't care less about the bolts in Malham, but then it's a subject that tends to breed scant regard for the petty affairs of man.