I've met all kinds.
People who are stellar managers, have extremely high empathy and EQ, understand their engineers, prop them up, help their career, guide them toward both professional and personal growth. They also did not have a single ounce of leadership or charisma in them, very low technical chops, no vision, and not interested in providing team leadership.
I've also met stellar leaders, visionaries, who inspired, entranced teams with every single word that came out of their mouth. They provided short and long term directions, technical and product guidance, motivation. And they were absolutely terrible human managers. Could not place themselves in other people's shoes. Didn't really care about managing the career or growth of people on their teams. Were only focused on matters that did not involve any human feelings.
These kinds of people both have their places and they complement each other wonderfully.
And sometimes, you find these two very distinct, polar opposite qualities, in one single person. But like I said, this is much more the exception than the rule.
And of course, the reality is that most people lie on a spectrum between these two extremes.