Spider-Man's uncle, Ben Parker. He only appeared in person in Amazing Fantasy #15 (and spoke only two lines in the entire comic), and died the same issue (although he has appeared in many Flashback stories and non-canonical stories involving alternate timelines). Despite his brief appearance, very few supporting characters in Spider-Man's life have had as much of an impact on him as a character, due to tragic way that he became a hero; his refusal to apprehend a criminal when he had the chance was what caused his uncle to be murdered. (Contrary to popular belief, the phrase "With great power comes great responsibility" cannot truly be attributed to Ben; the phrase first appeared as a narrative in the final panel of the comic.)
The same could be said for the nameless burglar who committed the crime. He did appear in a later story (where the motive for the burglary was revealed), but it didn't change the fact that he was nothing more than a common thug. Still, given the impact that this common thug had on Spider-Man's life, he could well be considered the greatest enemy the hero ever faced.
Edgar Vargus from Johnny the Homicidal Maniac appeared in only one strip in volume four, yet he's still popular in fanart and fanfiction to this day.
Anytime Batman cameos in someone else's book, especially since, away from home, he gets to be hilarious.
Matches Malone. Both the actual character, who dies after 6 panels, and Batman's sleazy arsonist/street hustler disguise who's always around when something's being planned and always has an excuse for why he's not drinking.
Nazi dinosaur Tyrannosaurus Reich. Only appears in two issues of Major Bummer and his concept is as awesomely cool as it is utterly ridiculous.
Neil Gaiman specifically mentions that he didn't have Death appear more often in The Sandman because he didn't want to water down the effect and specialness of her appearances. He still found a way for her to show up in all of the trade paperbacks, however, even if sometimes it's just in a silent cameo.
Tif from Ironwood is a definite fan favourite, despite making only one appearance (and dying at the end of it). Bill Willingham has said that he has had more requests for sketches of Tif than any other character from the series (probably due to interesting physical appearance).
Transformers: More than Meets the Eye gives us Nautilator and Blip, two goofy Decepticons who come off as hilarious and lovable and are loved by fans. They appear for about a grand total of two pages in a single issue, and their sole role is seeing some Autobots coming towards them and than getting killed by Whirl and Cyclonus during a big fight.
Flywheels is only in two issues before his horrible demise, but he comes across as one of the funniest and most likable Scavengers.
Glitch, an adorkable bot with the ability to stop any machinery with his touch, who only appears in the flashback story 'Shadowplay', and has yet to reappear since.
He has since reappeared, in something of a Wham scene.
An inanimate object managed to become this: The My First Blaster only gets fired twice and then never used again, but it instantly caught on with the fans.
The Duobots are this. They seem to just be a case of Those Two Guys, with the Running Gag that no one can tell them apart despite one being red and one being blue, but turn out to be an example Small Role, Big Impact. Ore dies almost immediately, suffering a Portal Cut when he messes with the Lost Light's quantum engines, and Shock shortly afterward, killed by a Sparkeater as he eulogizes Ore. Turns out they were agents of Prowl, and their actions kicked off the entire plot, sending the Lost Light off course, and creating a quantum duplicate. They are also Skids' handlers, and the reason he gets involved in the plot. Ore briefly comes back to life due to the presence of a Titan, and then is taken "away", so he may still be out there somewhere.
Kick-Off, who shows up for two pages in the first issue, then again in issue three when we find his corpse.
Squadron X, a gang of one-scene wonders from The Transformers, who appear only in flashbacks describing how they died, managed to become this by sheer value of their obscurity.
Brainstorm, the hilariously insane weapons inventor who enjoys making weapons that shouldn't be made, only shows up for a few paragraphs in a prose story included in the collected edition. He eventually becomes a main character in Transformers: More than Meets the Eye, where he continues with his inventive madness.
The Transformers (IDW) had a tie-in book titled Transformers: Ironhide. Drag Strip, an extremely minor Decepticon only used to fill out crowds and to serve as Menasor's right arm, get's a single non-speaking appearance in a flashback sequence in issue 2. It's just a minor shot of him walking down a red carpet smirking like he's the king of the world, all the while the narration reveals him to be a dangerous and proficient racer up to par with Blurr.
Infinite Crisis has Wonder Woman. The original, Golden Age Wonder Woman, who appears for a few pages to talk to her modern counterpart before vanishing from existence.
Also Joker at the end. Technically it's two-scenes and more of a Chekhov's Gun deal, but it's one of the most memorable parts of the comic and doesn't really have much to do with the flow of the main plot.