I once wrote an article for another forum site years back on him, Palumbo, Jindrak, Stasiak and Stromboli, how they were the young and probable future stars of WCW, if it survived, yet they never seemed to get off the ground in WWE. That shows that they either, weren't good enough to begin with, or WWE really is against WCW grown talents. I think it's a bit of both, but in reference to your question, I don't think I'd name him the greatest-never was, it honestly depends on how you use "never was", how long exactly are we talking, 3, 6 months, under a year, and by never was do we mean never been world champion or any kind of champion. I honestly believe his former partner Mark Jindrak was the best out of the five, and I'd consider Mark equally in the "never-was" category, even though he did wrestle longer in the WWE. And O'Haire did have successful runs in WCW as tag champ, and in the WWE solid matches with Kane and Undertaker for the WCW tag titles, before injuries derailed him. Like you said, he came back in 2003 with a great gimmick but management never got behind the gimmick really, and dropped much of it and instead had him managed by Piper, which did turn out to be beneficial for him, till Piper was released, then he really began to just float around and only appear on Velocity destroying wrestlers. 2003 was a weird year as WWE tried out a bunch of different talents (see Nathan Jones, Kenzo Suzuki, Mordecai) and kinda put O'Haire to the back a bit. They must've had plans for him as in his WWE career he's never been pinned in a televised match. Then a motorcycle accident put him out for a while, and he was released not long after he came back and now he's done.
So yea, it's hard to say who's the greatest "never was", and I don't think he is, but he's up there.