While I as a player would love it and all the character definition and drama it would bring, I've also gamed with plenty who would be "so I can either have a permanent negative to my character, or just roll up a new character? Pfft, new character".
Which means that all the character arcs, bonds with other characters and NPCs, and all the rest go away.
Now, for any particular table this may be a moot point. You know your table best, this may not affect them. In which case you're fine.
If it could be an issue (or for wider adoption), I'd suggest either/both:
1. Increase the frequency of the good results, so that players feel they have a chance to get something good. (And hope you don't have the type of player who will go for it, and then intentionally "accidentally" kill them off if they roll poorly.)
2. Have that replacement characters come in with some disadvantage (say, one level below the lowest level party member.)
As a separate suggestion you might have some lesser effects - ones that linger for days or a level or until a remove curse / greater restoration but aren't permanent. And some mixed - you detect as undead and trigger anti-undead things, but undead aren't immediately hostile to you...
EDIT: I wouldn't have these come up for the Revivify spell.