Are there any great LEGO designers that are well-known?
As there are more and more LEGO fans who become designers, some of them are already well known from the community and are very dedicated to fans, going to events and the like. They are actually just regular people and always have an amused look if you treat them like they have ascended to godhood status or something.

A few of them in no particular order:


Pierre Normandin

Pierre works on CITY and is known among others for 10129 Maersk Train and 3677 Red Cargo Train.
Mark Stafford

Mark works on most fantasy themes and keeps a list of his sets, also on brickset
Melody Caddick

We owe Mel some exlusive sets, including 3300003 LEGO Brand Retail Store, 3300001 Brickley and some (if not all, I'm not sure) Master Builder Academy sets. Mel also finalised the design of the first ever LEGO cuusoo set: 21101 Hayabusa
Megan Rothrock

Megan worked for the ill-fated LEGO Universe MMORPG, but doesn't work for LEGO any longer. She focuses on LEGO fan events and is now publishing her own book.
Jamie Berard, as cited in another answer
Markus Kossman

Markus is responsible for some huge Technic sets, including [8421 Mobile Crane] and the first Power Functions sets, 8275 Motorized Bulldozer


As you can see, LEGO now has online profiles for Technic and Creator designers (maybe others, but I didn't see any), and of course the LEGO Designer Videos Youtube channel.

Also, LEGO has also issued sets designed by fans, which is the closest you can be of being a designer without being actually one. Note that some of the names on that list did eventually become full-fledged designers themselves.

To end, a little piece of trivia: the license plates on some vehicles usually bear the initials of the designer of the set. Have fun hunting them...