There have been so many of these in my campaigns.  In my Albion game, most recently, there was "el cornwalense" as my players called him (that is, "the guy from Cornwall").  He appeared in one adventure originally, as a guide/scout while they were on a military mission in Brittanie.  The only thing he had was really excellent direction sense.  Uncanny direction sense (at first, I rolled for it, but after a bit I just decided the guy was a human GPS system).  I made him obsessed with direction and always pointing out possible routes the PCs could take to get anywhere (regardless of how ridiculously short, or far it was).  My intention was that it would be annoying, but the PCs loved it and took him on as a team retainer.  He lasted for quite a few session before being unfortunately killed, and I think they mourned him more than they did some of their own PC-deaths.

Not sure if this counts but an NPC who lasted about 10 or 11 sessions of my current campaign was a young boy, the son of a powerful and well-connected witch. He ran off with the PCs and his mother hunted them down. He was a clever, articulate boy but he had no special powers though he did have a ghost looking out for him for part of the time (only at night of course). The PCs started off by doing everything they could to help him, but in the end one of them shot him dead with a crossbow. He was by that time commanding a force of men bent on killing the PCs.

For a while he was my mouthpiece in the party, effectively my GMPC for prompting the players to discuss particular things, though weak and powerless and totally at the PCs' mercy. He was at the heart of the party's quest in a way, but in the end they weren't able to control his state of mind and things went sour between him and them from there. He went from being a relatively happy go lucky child to being deeply traumatised (all the PCs' fault) and him having to face his trauma probably made him more memorable.

One of the most significant ones ever was in the Port Blacksand campaign, where the PCs went up against the Beggar's Guild, and there was one beggar they just could not off in a fight. He had no special stats, it was just tremendously bad luck. He ran off cackling madly.

Later on, he encountered the PCs again, more cackling, more missed rolls.

After that, of course, the Beggars made him their new Beggar-King.  
The one PC most determined to off the guy kept trying to hunt him down but to no avail, and that particular PC was actually killed by the beggar, by a surprise sniper shot with a crossbow in the back, in the very last adventure of that campaign.

He was one of the most memorable villains of any fantasy campaign I ever ran.