There's a lot of good food for thoughts in this thread...
IIRC, the OP's idea is that saving throws can be boring if they don't have enough dramatic consequences for failure.
The focus on the discussion is then maybe shifted towards
mechanics but clearly it's also about the
narrative.
Personally, I don't think it really made a difference when they replaced saving throws rolled by the target with spell checks rolled by the caster. It might feel better for a player to roll the dice for the spells she cast instead of having the DM roll for the target, but what about when the target is the PC? Does the player now feel better that instead of being told "roll a saving throw" the DM simply announces "you're paralyzed/petrified/dead, I just rolled it behind the screen"? Instead, if the players really always want to roll, then a variant rule that works differently in the two cases would be better (that is, use saving throws when the PC is the target, use spell checks when the PC is the caster).
Anyway, IMHO saving throws were defensive in nature since the early days of D&D exactly because from a
narrative point of view it's more about how the target defends herself from the effect, than how the effect is delivered.
Let me try to elaborate...
With a
weapon attack, there is some (small) room for narrating the attack action. I suppose in most cases players just say "I shoot an arrow/swing my sword". But if they want, they can slightly flourish their description e.g. "I lounge and stab my sword to the orc's chest" rather than "I swing widely at the orc" or even "I bang the orc's head". The default core game of D&D never had called shot, so it doesn't really matter to aim at the head or something else, but at least you can
describe it if you want. The target can also describe how eventually manages
not to get hit, even if AC is static and not rolled: you can describe it either as dodging the blow, the armor absorbing it, the shield intercepting it, the target's weapon deflecting it... I guess every gaming group has their preference on whether describing every attack or not (or maybe only the important ones), but my remark here is that there is room for description on either side. Note that there are games where the defender also rolls, but from a mathematical point of you one roll is enough.
With a
spell attack IMHO the narrative room is a lot more on the target. Say that a spellcaster is casting a Fireball, how much room for narrative do you have on the caster's side beyond "I hurl the Fireball"? On the other hand, there is more room for description on how the target saves: "I hide behind by shield", "I crouch down", "I dodge the flames", "I just shrug it off, I don't care if it burns!". In theory spells should be complicated, but I can't really think of a way to vary the narrative of the casting itself. I can flourish the description of the gestures or even come up with verbal components, but these are not exactly what make the spell succeed or fail... it's what the target does!
That said, of course what happens
after the casting can be boring depending also on the mechanics... but here things get complicated. Some gaming groups will be bored by having too frequent characters deaths/sucks, and other gaming groups will be bored by having too rare characters deaths/sucks! Very hard to find a setup that works for everyone... it even depends on how many encounters per day, and how often your monsters cast spells at all.
One small (old) tip that comes to my mind is... don't tell the players what they're rolling a save for
Drop some fancy description such as "you suddenly feel an unearthly shiver through your spine, as if your life essence is drained away... make a Con save!". Not knowing in advance can make it scary, "please let it not be Finger of Death..." and when they fail they'll be relieved if it's only a minor effect.