John Terry will probably always be my least favorite Leiter. He is THE weakest part of The Living Daylights and that's saying a lot when Joe Don Baker's Brad Whitaker is one of the two main villains.
Moneypenny?
Then you have Joe Don Baker in Goldeneye and Michael Madsen in Die Another Day as Leiter-substitutes. I always assumed that they didn’t want to either have a crippled Leiter or to ignore the events of LTK & so it was simpler just to have new American spies for Bond to deal with. I think I remember Madsen in particular saying that he hoped to have a recurring role, but once Brosnan was recast & the series was rebooted, that was that.
God I hated Madsen in DAD, he just felt wrong. In contrast much as Joe Don Baker isn't a great villain in the Bond pantheon, I always really liked Jack Wade and thought he and Brosnan had great chemistry. It's a shame he didn't come back for DAD but I guess they needed a higher ranking CIA guy.