One of the keys I've noticed over the years I've been GMing to a truly great session, one that the players all love and talk about for years, is that you need to make them feel like John McClane from Die Hard. When I say Die Hard, I mean specifically that. Not the sequels. The original. Maybe two and three to some extent, but somewhere along the way - and that changes depending on whom you ask - the writers forgot what made Die Hard and John McClane work so well, and that essence is what you need to catch for a story line that will leave players grinning and going "Damn, that was awesome."
So, About Die Hard
At its core Die Hard is a movie about perseverence. John McClane is not some super commando like in a number of Arnold Schwarzenegger and Sylvester Stallone movies. He's not a super cop, at least at first. He is a regular NYPD detective on vacation in Los Angeles when he runs into an impossible situation.
Through the story John gets beat up, blown up, cut up, shot, and generally put through the ringer three or four times. He goes in fresh. He comes out battered, bruised, limping, and barely able to move forward from exhaustion. He's also victorious.
That's what you need to catch.
Beaten But Victorious
This is what, I'm convinced, almost every player wants. This is especially true for the Story players, but it also rings true for the Challenge players, the mechanical munchkins, and everyone else. They want to be pushed to the limit. They want to be left with just a finger hold keeping them attached to the outcropping of rock hanging over the abyss, but they want to win doing it.
They want to be on the razor's edge. They don't want to fall off the razor. That is where the challenge comes in.
Pulling this off is not an easy act. It takes knowledge of your players, their characters, the system, probability, and tons of back up plans. Even then, you're going to fail. Sometimes the dice will betray you and the PCs will clobber everything. Sometimes the dice will betray the PCs and it won't just push them to the brink, it will load them in a basket with all the dirty laundry and throw them over the edge and into sweet nothingness.
However, get it right and you'll see the grins on their faces. Get it right and they'll feel they snatched victory out of the jaws of defeat. They'll feel like they cheated death. They'll feel like they won not because the system was weighted for it, and not because luck was on their side, but because they used their characters well, used their abilities well, and spent their resources well.
It's a great feeling, on both sides of the screen.
A Word of Caution
One thing to remember though, is that you can't do this with every adventure or arc. You do this for your capstone piece. You do this for the big, grand story arcs. You do it all the time and the PCs won't feel heroic, they'll feel out of their league. They'll think you're keeping them alive. And that's not good. Worst of all, the PLAYERS may feel exhausted from it, and tired players can lose interest in the game. So use this, but don't use it all the time.
Hit Them High
Hit the PCs where they expect to get hit. Hit their HP. Hit their resources. Run them down until they have nothing left in the bag of tricks. Burn through those hourly, daily, and weekly abilities. Drop them low. Hit them with exhaustion levels. Play the scenario like Bane in Knightfall. Don't give them time to rest. Things are going on. They don't have time to stop and breathe. They have to get through it.
Hit Them Low
Low blows are the ones that hurt the most, and the ones that catch people by surprise. Hit the PCs where they're not expecting. Do they have allies? Remove them. Turn friend into foe and see what happens. Endanger the PCs loved ones. Destroy their home. Take away their prized possessions. Ruin their reputation.
Be careful here you don't do it all or go overboard. You don't want to remove all progress, but you do want to hurt them emotionally. The thing is, you want to hurt the CHARACTERs emotionally, not the players. This is where timing and pacing comes in. It's one thing to take away the Paladin's prized Holy Avenger at a time when he needs it most, just make sure you talk to the Paladin out of character, and make sure you are setting it up for that sword to come back at a time when the character needs it most and in the most epic fashion.
Hurt Them. Don't Kill Them
Hurt your PCs. Make them feel it. Drag them along the coals. But don't kill them. When they win, against all odds, let them hold their clenched fists high in victory before they collapse in victory, and let that victory linger for a while...like a day, maybe two, and then the world can call again.
Let your Players feel like John McClane. They'll love it.