Like you said I think intrinsic motivation matters more than where you go. I went to a "shitty" (at least that's what CMU kids have said more than once to me to my face) school, University of Pittsburgh. I did fun research during undergrad and managed to get a job after graduating easily enough. At that job, different CMU graduates called me dumb for going to a public high school and university, uncultured, and literally laughed in my face when I said that I wanted to switch careers and be a surgeon. During med school interviews, a Harvard girl immediately turned around and stopped talking to me after she asked where I went to college. Now I'm in medical school at a ~40th ranked place (no not Harvard or Penn, shocker) well on my way to my career goals. I've done deep learning research at a top children's hospital and am scoring great on practice board exams.

I guess my point is that people will always find something to shit on you for, and the bias against lower ranked schools comes from above. The state schools like Penn State, OSU, Pitt, Michigan, Florida, Illinois etc. are enormous institutions that have a good amount of research going on and are definitely accessible to a motivated high school student. So if you don't get in to an Ivy League school, your opportunities aren't going to be limited as much as you think, and rejection is a great motivator.