First, I love E.O. Wilson. I can pick up and read "Consilience" from almost anywhere and find a gem or two in it. But he is a gentle idealist, a good man who's made good in the world. And his words are at least in part a statement about
should be, not just about what
is. And I agree with him. Professor and Scientist are ultimately distinct because the former is bound to academia's peculiar and dark rules, and the latter is not.
Startups famously cannot afford to do science, Big Corp. doesn't have an R&D budget, academia is stultifying, so that leaves...Gentleman Hobby Scientists! The kind of people who are a software architect by day and watch 3blue1brown at night, or watches Steve Caroll's "Biggest Ideas in the Universe" series (great, btw). The kind of people who 20% time was made for. The kind of people who kick themselves for not dropping out in 1996 when they had solid funding for an internet startup available, but they wanted to finish their degree.
But yeah, "greatness" is always going to be about luck more than anything. The only consolation is that no matter how great you become, on a long enough timeline you will be forgotten. Heck, go out on the street and ask about "minor" scientists like Lavoisier or Leibniz or Gauss or Bohr or Brahe or, heck, Archimedes. You will get blank stares, or worse, ridiculed! So yeah, "greatness" (which they used to call "immortality", interestingly) is a pretty bad reason to do science, in my opinion. OTOH if you want to push the boundaries of human knowledge, and want to be the first to see it (and also one of the first to appreciate the progress of others), then that sounds good to me.