I certainly get the idea that some want success to be a near-constant and failure to be just a speed bump rather than a hard loss.
Note I say failure rather than consequences. They're different. Failure happens here and now and has to be dealt with here and now; consequences happen later and thus can sometimes be mitigated or avoided.
I've certainly found that both success and failure have to be present for true enjoyment.
If success is constant with failure never an occurrence then the success doesn't actually mean anything - it's a given. Players will likely have less fun when there is no true challenge to overcome.
Conversely, if failure is constant - no matter what the players do they never seem to get even a small scale win condition - that too, is a huge fun dampener as players will likely stop putting any sort of effort in.
The key is to ensure that success is possible but failure is also possible. And ideally success is more possible than failure and happens more often but that failure never ceases to be a possibility and still occurs enough for the players to not take success for, in any way, granted.
The above seems obvious, but I've seen way to many campaigns (not just D&D) where even small scale success is so fleeting that players get bogged down and just lose interest.