Fame is perhaps the wrong word: I don’t think anybody starts an open source project thinking it’s going to make them famous in the sense that movie stars and pop stars are famous - but I do believe that there’s a desire for recognition and acknowledgement that drives a lot of people to share their code. Larry Wall, the creator of Perl, famously proposed that the three great virtues of a programmer were “laziness, impatience, and hubris” - laziness drives people to write code that makes their lives easier, impatience drives them to make that code fast and reliable, and hubris drives them to write code they want to show off to their peers.

As for “does it work” - that depends on your definition of success. Sure, there are open source developers who are very well known, but I think in a lot of cases that reputation is as much down to their “personal brand”, their presence on social media, posts to mailing lists, etc. as it is to their code.

Put it this way: if your only objective is to be famous, there are probably much easier ways to achieve it than starting an open source software project. 😉