Kirk's reverence seems to be an outgrowth following the miraculous return of Scotty in 2369, as he was barely a figure of note in 2364 (per "The Naked Now"), and since the show has a narrow focus on Starfleet officers, it seems that most of the populace would probably not even recognize his name. He certainly doesn't seem to have any awards or ships or shuttles named after him (until the 32nd century), unlike his predecessor Pike, who is explicitly listed as one of the greatest Captains in Starfleet history. Kirk was operating in the shadow of Pike and April, and his five-year mission adventures probably pale in comparison to there's. Although they were likely romanticized by Scotty and even more so after Kirk's own miraculous return (and second martyrdom) in 2371.
Kirk was famous in his own right, amongst the Klingons and for the events of The Voyage Home (and probably The Motion Picture), but these were contemporary situations forgotten until the nostalgia craze of the 2370s and '80s that we see glimpses of in Voyager and full force in Lower Decks. Kirk also had a much more exciting pre-TOS career, if his awards given in Court Martial are any indication.
Picard seems to be well over Kirk in Earth-saving moments and overall achievements (it helps that his career is twice as long), but in STPicard, he's barely recognized, even at Starfleet Headquarters. By Neo-Discovery days, he's just "some Admiral" mentioned, but that's understandable after such a lengthy length of time.
Archer was President (I guess), but his Presidency might be as consequential as Grant's or more likely Hayes'. His Captaincy is all that's really referenced in Discovery (and never again, but we all know why). No reference to anything he may or may not have done in the Romulan Wars, and TATV implies he didn't do much. Those wars may have wiped away his accomplishments of those first few years as more valiant Captains and more grand Presidents would come to the forefront of history.
Burnham is an unknown to history, probably remembered just for her mutiny (legally wiped from the record), and likely death following the Klingon War.
I think, Sisko has a good cause for being the most historically relevant and praised Captain from amongst all. Janeway has some pop culture bonafides, but she isn't a hero of the people just for being lost in space a few years.
My list of most revered Captains from a perspective of 2399 Earthican:
1. Sisko - Cause of, and solution to, the Dominion War
2. Picard - Did some anti-Borg stuff, I think
3. Kirk - Saved Earth according to the history books
4. Janeway - Popular dramas are being reported of her time in space
5. Archer - old-timey Captain from the long, long ago.
6. Burnham - Who?
Also, it goes without saying that Captain Freeman is one of thousands of minor Captains with no awareness by the general public or even Starfleet insiders. Captain Rios is just a freight hauler and disgraced officer on the periphery. Whatever happens in Strange New Worlds will probably re-elevate Pike to the top of the list (he's already certainly the most famous active Captain in Starfleet... something that has never been true of any Captain in any previous show, except for maybe Archer). Anything that happens in Section 31 will be classified, and Prodigy seems to be low stakes stuff.
