The Chronicles of Riddick had a mode kind of like this, but with a little faster pace. It was called capture/kill the Riddick. You started off as one of four mercs or Riddick. As the mercs you started above ground picked your guns and dropped down into a pitch black mine shaft. 
 
Also I played so much Chaos Theory with my friend it was crazy. I never beat the single player though...I remember when that game was coming out Donny (my friend) watched me beat Pandora Tomorrow the weekend before Chaos Theory came out so when I picked it up we just started right on the co op.

I never played any of the Splinter Cell games after the first one (though I'm thinking of coming back for Conviction), but from your description, I see an inherent problem in Pandora Tomorrow's multiplayer: it's only fun when you're a Spy. I'm going to guess that it alternates teams between rounds, because it seems to me that the only reason anyone would play as a Merc would be if they had to, so that both teams could have their turn on the fun side. :P 
 
I'd prefer a multiplayer system where it was fun for players on both sides, though admittedly I can't think of how you'd do that.

Oh man I played so much Pandora tomorrow.  It blew my mind every time I played it back then.  It was one of the first Xbox Live games I played next to Counter Strike.  It's also the only Splinter Cell game I've played mulitplayer, and from what I've heard around the internet it was the best so I'm okay with that.

I didn't ever get the chance to play Pandora Tomorrow, but the Spy class in Chaos Theory was infinitely more fun than the Soldier class. 
 
Not exactly multiplayer, but the stealth missions in the Modern Warfare campaigns have been handled really well.

I love it! There is nothing more satisfying for myself than sneaking up on an enemy player, taking them out, and escaping to safety without alerting anyone else. It's even better when I successfully take out the person who comes looking for them. This is why Splinter Cell: Pandora Tomorrow is one of the best multiplayer experiences I have ever had to date. Released in 2004, Pandora Tomorrow introduced a unique multiplayer mode that has yet to be matched by another series. Titled "Spies Vs. Mercs", the gameplay style was drastically different based on who you were playing as. Playing as a Merc, you were placed into a first-person view with a fully-automated machine gun and were given various pieces of additional equipment like grenades and mines. As a Spy, you played from a third-person perspective with a Taser gun and had equipment like flashbangs and sticky cameras. Each team consisted of only two players, and every player had limited lives. Mercs had three each, and Spies had four. Once you ran out of lives, you were done until the next match.

Playing as a Merc was scary. The first-person view naturally limited what you could see around you, and there was always this constant fear that a spy was about to grab you from behind and break your neck. Thankfully, some of this fear was negated due to the greater firepower you had, but greater firepower doesn't mean shit when in a chokehold.

Playing as a Spy, however, was where the game really shined for me. As a Spy, you always felt in control. Any good stealth player knows the key to being successful is by staying hidden but always knowing where your enemies are. I was always filled with glee as I watched my enemies scurry about trying to locate me, knowing that they will fail and that in a few seconds, they'll be dead. When switching sides though, it was always unnerving knowing that somewhere, anywhere, someone was watching everything I do, just waiting for that opportune moment to strike. 

Thankfully, I don't have to wait until February to get my fix. While not strictly stealth games, there are some titles out there that satisfy my need with a friend. Modern Warfare 2 is the most obvious one at the moment. Replicating scenarios from the campaign, there are a few Spec Ops missions in which stealth in the primary focus. Playing these on Veteran with a friend is a ton of fun, and if we mess up, we still have a chance to survive without having to start over. The real fun, though, comes from not getting caught. Situations often play out like this:

 "Stop! You see those four guys over there?"

"No, where?"

"Look to your right a little more."

"Ah ok, yeah, I see them."

"Ok, I'll take the two on the left, you take the two on the right, good?"

"Gotcha."

"Ok, on three. One...two...three!"

 *All four enemies drop simultaneously. None of the surrounding enemies are alerted.*

 "Beautiful."