In the sciences, it is possible to earn quite a lot in textbook royalties, as the author of a book for an introductory course that is widely adopted, so that there are hundreds of thousands of purchasers each year. In addition to the physics text by Halliday and Resnick, other examples that come to mind are the calculus text by Thomas, the calculus text by Stewart, the organic chemistry text by Morrison and Boyd, Jackson’s Classical Electrodynamics, Merzbacher’s Quantum Mechanics, and Huang’s Statistical Mechanics. (Some of these may no longer be in use, but they had runs of about 20 years or so). A typical general chemistry text is likely to have 6 or 7 authors. The standard royalties are split among them–they don’t gain extra royalties for the extra authors.
From a research monograph, I’d guess one would be lucky to earn $1000 in royalties, total. There are always library sales.
Cobrat is correct that going on sabbatical to write an undergraduate text, or even writing an undergraduate text without going on sabbatical, would not be a “career move” for a science faculty member at a research university. Writing a research monograph could be a good move (YMMV), but the main purpose of a sabbatical in the sciences is to gain expertise in a new, but related specialty.
Sometimes people write undergraduate texts because they don’t like what’s available in the area, though.
One of my undergrad math profs at a large, public research university wrote a text on topology. He donated the royalties from sales in the same state to the university, on the grounds that the people of the state had paid his salary. He kept the royalties from sales in other states. I don’t think it was a lot of money, in any case.