With few exceptions, Escort Missions tend to be the bane of my gaming experience. The shining example that stands out to me though is Ico.
Ico is essentially one big escort mission. Yorda is pretty much defenseless from the mean things that lurk in Ico's wonderfully depicted world, but the game is also built with this in mind. You can help Yorda over obstacles and help her navigate troubles, you're given a nice warning when she's in danger, and generally things just don't feel like that much of a chore. But the game was built around this and for this, and as the editorial touches upon, making the AI and game mechanics function for an escort type mission isn't as easy as it sounds.
Half-Life 2 and it's subsequent episodes also do a decent job with this with Alyx. There are sequences in the games where you have to work with her, but she tends to follow you with some intelligence, and she isn't just a sitting duck. In Half Life 2 she was too strong though (invincible, from what I could tell, I let her be my bullet sponge and do most of the fighting when she was around, why waste my precious ammo). But this was tweaked in Episode I. She is quite strong, and her gun is potent, but she isn't invincible, as I was startled to learn in a dark garage as we were rushed by zombies and I decided that the out of the way corner was the most prudent place for me to be and left her in the darkness to fight off the hoard. You fail is she dies, and your job is to get her and yourself to a point, but she isn't just a lamb waiting to die or be tipped over, she's strong enough to take hits when you aren't sitting over her and can face off against enemies as well. You just have to strike a balance, so that the character isn't so weak that you have to hover over them, and they aren't so strong they can be used as your shield.
With that said, on the other end of the spectrum, "The Control Room" in GoldenEye for the N64, particularly on 00 Agent stands out in my mind of a shining example of just how bad escort missions can be.
Natalia generally moves at her own pace, and doesn't care much for the danger that surrounds her. If a single enemy soldier has a shot, usually he will unload his AK-47 knock off into her and dispatch her in pretty short order; her pain sounds playing in rapid order, one for each hit she takes before she collapses dead in a manner of seconds. You navigate a series of corridors and decent sized rooms, but nothing that isn't impossible. Then comes for Natalia to use her computer abilities, near the end of the mission.
The only terminal she can use is in the middle of a very large room. There is a large monitor on one wall, so technically about 180 degrees are safe. But the other 180 degrees are filled with glass that you cannot see through until it's destroyed (but enemies will fire through at will) and if that weren't bad enough, it is two stories of windows. They pop out, give her a burst, and kill her. Ultimately it came down to spinning like a top trying to spot them and trying to remember where and when they appear.
GoldenEye had no in game save features or checkpoints, so once you failed, you started from the beginning all over again. An exercise in frustration and heartache, I remember it a full ten years later like it was yesterday.
Guard the convoy, rescue the child, save the professor, in whatever guise they are, the escort missions are usually not a change for the better. They do serve the purpose of a change of pace, and when done right can make the player feel like they aren't alone against the world, but more often than not they're just Adventures in Babysitting with more guns and less Albert Collins.
dmc0419 brought up Ico while I was rambling about GoldenEye but I completely agree with him/her.