At first, Pinhead obliges Jophiel's request to return Sean to the living so he can score the good side some souls, but when Sean regains consciousness, he's immediately waxed by Detective Edgerton, who awakens after being knocked out by Sean during his dramatic reveal about secretly being the big bad they've all been looking for.
Sean's instant demise puts him back in Pinhead's clutches — and angers Jophiel, who threatens to bring down the wrath of her boss for his insolence. Pinhead decides he's got nothing to lose, so he has nothing to fear from her. He's already in Hell and was basically built to endure such suffering, so how much worse could it get for him? Not only does he ignore her warnings, Pinhead also proceeds to throw the angel into his chains and lets them rip her to shreds. Even the Auditor knows his boss has made a massive mistake.
As he points out, Jophiel was the angel who banished humans from the Garden of Eden, and if she could do it to them, she could do something similar to Pinhead. Just as all those he's judged have awoken to the harsh consequences of their mistakes, the last scene finds a humanized version of Pinhead banished from his realm of "sweet suffering" and sent to the mortal realm.
With that, Pinhead as we've known him for 10 films (and counting) appears to be forever changed for his decision to test God — which means we can probably count on his next adventure involving him trying to regain his status in the underworld. On the other hand, the post-credits scene opens up the door for the Auditor to take over altogether, as he's shown in Hanover, Germany, welcoming two unsuspecting missionaries into his lethal lair.