If you make the toolset good enough then SQL Server becomes that can be cradle to grave.

It can cope with numpties but in the hands of a master it can perform wonders.

You'd be amazed how many IT professionals don't know how strings work or what an interupt vector is, or have a rudimentary understanding of storage, or latency or a whole host of other nitty gritty details. They simply haven't needed to.

Would they have been better if they did understand the nitty gritty? I'd hope so if they had a desire to become tech leads and architects, but probably not if they had an aspiration for management.

I get the impression that what you are really asking is to be recognised as a skilled professional rather than a replaceable nobody. For that you have to develop soft skills or be golden boy to someone with the soft skills. I know it isn't fair but to quote a song "life's a bitch and then you die".