As I posted in answer to another comment, more often than not, schools want to avoid the dramas associated with plagiarism scandals. That is why schools like Harvard will prompt researchers accused of fraudulent behavior, such as Marc Hauser, to resign, rather than go through tenure revocation procedures.
But losing one's job is fairly likely, and an unofficial blacklisting is almost certain to result.
One other consequence, though, is often forgotten: the peripheral damage of academic fraud cases. Sensationalized results, such as those in the Jan Hendrik Schön and Hwang Woo-Suk cases, led many graduate students to embark on projects in those disciplines trying to reproduce and expand upon the promises implied by those projects. When those projects collapsed, a lot of graduate students were left in the lurch.