It can when the wrong product is used or the assembly is not designed to dry. Closed-cell foam on a roof deck creates an unvented assembly, which works when detailed correctly and traps moisture when it is not.
More detail
The concern is legitimate and the answer is design-dependent rather than product-dependent. A conventional attic is vented: soffit to ridge airflow carries away moisture and heat, and the insulation sits on the attic floor. Foaming the underside of the roof deck converts that to an unvented conditioned attic, where the insulation follows the roofline and the attic becomes part of the conditioned space. That is a legitimate and common assembly, and it changes the drying path fundamentally. The deck can no longer dry upward through ventilation, so any moisture that reaches it has to dry inward, or not at all. What makes it work. Sufficient closed-cell thickness against the deck to keep the sheathing above the dew point in winter, which is a climate-zone-specific calculation rather than a rule of thumb, and in Climate Zone 5A it is a real calculation rather than a default thickness. Complete sealing at eaves, gables, and penetrations, since a partial job creates a cold spot exactly where moisture will find it. Confirmation that the existing roof does not leak, because an unvented assembly hides a leak that a vented attic would reveal as a stain. What makes it fail. Open-cell foam directly against a roof deck in a cold climate without a vapor control layer, which lets interior moisture reach cold sheathing. Insufficient thickness. Spraying over sheathing that is already wet. Leaving the soffit vents open into a now-unvented assembly. One practical consideration for future roof work: a foamed deck means a roofer cannot inspect the underside for leaks, and a re-roof is done from above with no interior check. That is a maintenance tradeoff worth understanding before choosing the assembly, not a reason to avoid it.