"Local celebrities" might be overstating my take/taste on the matter, but I, like some others in the thread, think that in the milieu of the common D&D world, yes, these bizarre outsiders come with a bunch of potential downsides as mentioned in the OP, but also the potential upside of solving the local problem du jour. I'm not familiar with The Witcher (despite being a life-long video gamer as well, I have never been interested in the game or the books) to contextualize the overall grimdarkness of that world, but specifically in D&D and similar, the world seems beset with threats to the locals, immediate and existential, so I interpret the average response as something a bit more like reserved acceptance than outright antagonistic distrust. Of course, if the adventuring parties of the world are all roaming through the countryside, bringing chaos and leaving it in their wake as they follow only their id, communities may catch wise over the generations and come to outright distrust any outsider that looks capable of something more than farming and trading, but I think that in the commonly presented world, the myriad threats --- wolves, goblins, plague, evil sorcerers, necromancers, greedy lords, demigods with a bee in their bonnet, yada, yada, yada --- often overwhelm the mundane responses available to a community other than "hope a lord or adventuring party who is heroically capable comes around, or we get lucky and can make our own". I also think that the patternistic progression of random adventurer to notable problem-solver to storytelling inspiration to perhaps even probably-benovelent lord is a common chute, even if their capabilities and methods are alien to the rest of the society.
In short I think the world we are commonly presented with to run adventures in suggest that the world would be worse off without them, and that they are somewhat normal --- certainly there's enough world meta-infrastructure to support the idea that this need exists in many places. And again, if those adventurers cause too much trouble? Adventuring parties beget adventuring parties.