A lot of the vision was actually around building orbital solar arrays (remember the microwave power plant from sim city 2000?) to resolve the oil crisis.
The oil crisis went away and the microwave arrays idea didn't make much progress and shuttle' budget got cut until they could only do one vehicle, so they tried to combine both and have a reusable heavy lift manned space glider.
But because the microwave arrays weren't going to happen nor was the earlier L1 space station idea (and skylab deorbited before shuttle's first launch) they had nowhere to shuttle anything to, so instead they just ended up with a heavy lift LEO launcher that had people onboard for some reason.
Ofc they eventually got the ISS as a destination, much later.
There are many valid points about the difficulties they'll need to overcome for manned flights but there are likely many solutions as well. I wonder if it could be as simple as making a really big crew capsule capable of launch abort with its own thrusters? It would make the second stage partially single use but the capsule itself could be reused like they reuse the Dragon crew capsule.
As for large satellite launches, there's an obvious need for a larger faring volume when you look at the lengths we've had to go to in order to make payloads fit into the tiny ones currently available. Being able to launch a large telescope or solar array that needs minimal folding would be a huge cost and time saver. Having more and cheaper tonnage also saves money on materials used for manufacturing satellites, you can use less expensive metals/alloys and components if you have more weight budget. You can add more fuel to extend operational lifespan. It's also big enough that launching habitat modules becomes much more attractive. There's a lot we didn't do just because it was too expensive or simply impossible with the launch capability we had.
I have yet to be convinced that Starship’s payload capacity is actually a good thing, from a big picture perspective. It looks to me like a possible case of shouting, “I meant to do that!” after a pratfall. On a months-long trip to Mars, you need a vehicle that big for the sake of the human occupants. For launching Starlink, it’s less clear whether the ability to deploy so many satellites at once was worth the vehicle’s development costs. Perhaps it’s just inherited from an earlier iteration of the program that had different goals, and a more modest design would have been more economical. Worse, so far it seems like Starlink might be it. Most other use cases people have proposed for putting 40 tonnes in orbit on a regular cadence are the kind of highly speculative grasping at straws that accompanies any technically impressive solution in search of a problem.