If someone came into a highly-demanded (by the team itself) role and turned the responsibilities around into work the team can take care of on its own, that's valuable work for sure. But if this was just a role created by an executive as a result of, "We've always needed one of these", then maybe not so much.
You can create organizational chaos by skipping that last step, you may even achieve job security. But it's blatantly obvious to both your managers and coworkers if you fight to keep them off your turf. Don't expect respect or admiration.
Absolutely not. The jobs that are the most respectable are the ones where your job is to put yourself out of a job. Management can be a noble profession because of this.
I 100% agree they have scaled / transcended their “job” at that point.
Don't manage your work from a place of fear. If you really don't want to be promoted, that's fine. I respect people that have that opinion. But don't try to ensure job security by slowing the organization down to reassure yourself you're too essential to replace.
IMO, anyone who deprecates themselves from an important role within a team has successfully scaled themselves in a very interesting way (same output, but person is free to do other things). Such a person would seem very valuable and destined to scale in even more interesting ways.