To understand this, you need to realize that San Francisco politics is a perpetual no-holds-barred fight between two factions within the local Democratic Party: the "Moderates" and the "Progressives" (although the Progs also contain some members of other parties, like the Greens and the DSA). The Progs hate the Mods with a burning passion and are constantly trying to undercut them. Generally, Moderates tend to win city-wide races, while Progressives tend to win district supervisor races. Right now, the mayor's office and all state-level seats--including Scott Wiener, who authored the bill in question--are occupied by Mods, while the Progs have a veto-proof supermajority on the Board of Supervisors.

The Progressives tend to lean more NIMBY than their Moderate counterparts, and some of this opposition is certainly the result of that. (Gordon Mar, the president of the Board, is probably the biggest NIMBY of all the supervisors at the moment, possibly excepting Aaron Peskin.) But most of this opposition is purely tribal: the Progs on the board just want to snipe at Scott Wiener. Progs are not categorically opposed to housing: as others have pointed out, when Brisbane was waffling on approving market-rate housing just to the south of San Francisco, Jane Kim, a Progressive, threatened to annex the city!

Most of this anti-SB-50 resolution is properly understood as San Francisco tribalism more than any sort of policy grievance. SB 50 doesn't even have much if any of an effect on a lot of the districts these supervisors represent, either because they're exempted as "sensitive communities" (e.g. much of the district I live in, D10) or because the districts are already upzoned (e.g. the Mission).