Today Sid is ironically liked in an MST3K kind of way, or considered one of the worst wrestlers to ever get mega pushes.
There is a podcast dedicated to bad wrestling of the 1990s, and they just attempted to do a Sid match marathon.
I guess your Sid comparasion is akin to JvK's constant wondering how Chief Jay Strongbow is remembered fondly by fand s of the time and given strong pushes.
Sid wasn't ironically liked during his time. He was over. Everywhere. At times super over. With his look and weird charisma, it was a no brainer to push him to the top your cards. You don't have a guy like Sid and have him toiling away in the mid card for years. Of course he wasn't a great worker, but when has that ever been the most important thing about getting a push in America?
As part of the Skyscrapers and Horsemen, got SID SID SID chants a month into his JCP/WCW run. Went to WWE and was instantly more over than Hogan (who, granted, was on the decline, but still). Went back to WCW, got cheered over Sid. Went back to WWF in 1996, turned MSG into a bunch of screaming marks for a muscled up stiff who "can't work" over who was considered to be probably the best worker of the 90s in HBK in the smark capital of of the world. Went back to WCW, was the most over guy in the company besides Goldberg. That's not ironic. It's not ironic to understand why he kept getting the main event pushes in his career. Him being a flake was his biggest detriment, not his lack of ring skill.
Not to mention how batshit crazy people went for him in ECW.
But "weird charisma" also gets at why he was hard to book on top for a long period of time. He was not a great promo, which made it hard to build heated feuds. He couldn't really get across well his motivation - did he want a belt? To hurt people? To get revenge? He had something people responded to, but it was difficult to translate into an ongoing story.
He was probably also hurt by the frequent turns (a WCW trademark, although it happened in the WWF with him too). He could have been a Goldberg-esque babyface killer and made some money that way, but everyone seemed to think he could also be used as a heel monster for babyfaces to slay. But he was a bad heel worker, even by big guy standards. With the reps, he might have been decent working a Hogan-style babyface match, but he never did that for an extended period.