Picard was famous
In the TNG episode "Family", Picard returns to Earth for shore leave after events that included his capture and transformation by the Borg, and a Borg cube arriving at Earth to assimilate humanity — a catastrophe that was averted by the intervention of the Enterprise and her crew.
The episode gives us the strongest hints that Picard, by that point, had become well known back on Earth.
MARIE: The Mayor wants to give you a parade.
PICARD: A parade?
MARIE: Give you the keys to the city.
PICARD: No. No, no, no, no.
ROBERT: He just needs a little arm twisting, dear, coaxing.
PICARD: No. he does not. I'm here to rest and spend some time with my family.
MARIE: Well, I've already warned the Mayor not to make any plans without talking to us.
Granted, this is only Picard's hometown, but not everyone is famous in their own hometown (or at least if they are, often not for the right reasons).
Much more telling: when Picard meets his friend Louis about the project to create a new subcontinent on Earth, Louis indicates that the United Earth government is seeking Picard to take over this project, which is indicative of Picard's influence, reputation, and fame:
LOUIS: You know, it's such a shame. The government is looking for someone to take over the project. A real leader who'll go in there and get things moving. And they are looking for you, Jean-Luc. I know, I know, you'd never leave Starfleet.
PICARD: No, I'd never leave Starfleet.
A little later:
LOUIS: They want you. I mentioned your interest in the project, that's all. That's all I had to say. They jumped at the prospect.
Further along in the episode, Picard and his brother Robert have the following exchange:
ROBERT: Mind if I ask you a question? What the devil happened to you up there?
PICARD: Is this brotherly concern?
ROBERT: No. Curiosity. What did they do to you?
PICARD: You know what happened.
ROBERT: Not precisely. I gather you were hurt. Humiliated. I always thought you needed a little humiliation. Or was it humility? Either would do.
Their exchange suggests that some of the details regarding the Borg and the Battle of Wolf 359 were reported to ordinary citizens on Earth, likely via the Federation News Service. In particular, Picard's plight seems to have been reported to some extent. Since Robert and Picard were not really on speaking terms before this point, one must assume that Robert heard these details through the news.
Although current events (in particular, the Borg invasion of 2367) are certainly the main thrust behind Picard's fame on Earth, it does help that the Picard family is already famous:
- One Picard commanded a French warship at the Battle of Trafalgar.
- Another Picard won the Nobel Prize for Chemistry.
- Some more Picards settled the first Martian colonies.
- Chateau Picard seems to be a decent red wine.
Other senior officers
It is more difficult to ascertain whether the other senior officers of the Enterprise-D are famous on Earth, but we can make some reasonable speculations nonetheless:
- Riker might be regarded by the public as a hero for his actions as Captain of the Enterprise that led to the defeat of the Borg and the sparing of Earth from assimilation in 2367. (On top of this, Riker too had famous ancestors, namely Colonel Thaddius Riker, who led a battalion in the American Civil War.)
- One might suspect that Data, given his unique android nature, would be famous. It is conceivable that students might learn about him in school. Furthermore, Julian Bashir planned to publish an academic paper about the dreams Data had during their time together. Further still, the court case concerning whether or not Data was considered property would probably be considered a landmark case.
- As the only Klingon in Starfleet, Worf might also enjoy some notoriety back on Earth.
As for Laforge, Crusher, Troi: there isn't much information from which to draw conclusions about their fame (or lack thereof). I suspect, nevertheless, that the success of the Enterprise-D is probably much more closely associated with Picard than with any of the other officers on board.
Note: Some have asked about O'Brien. It is true that he has been called the "Hero of Setlik III" for his actions during the Battle of Setlik III in the first Federation-Cardassian War. This would make O'Brien famous on Setlik III, and perhaps within certain Starfleet circles, but I suspect fame on Earth (which is what the question is about) would be unlikely. Perhaps if he had attended the Aldebaran Music Academy like his father told him to, his music career would have taken off, and then he might have been famous throughout the Federation for other reasons!