Only the Crew Dragon and the Soyuz are designed for human spaceflight and capable of returning to Earth. The Cargo Dragon can also land, the rest are all expendable.

That's a lot - I did not realize that ISS is so busy of a space port.

Which of these are return vehicles (human-rated or otherwise)?

Just the two Dragons and the Soyuz, right?

Edit: neat that I get to call a spacecraft a “Dragon”.

Could people ride the Cargo Dragon down in an emergency?

Cargo Dragon doesn't have seats, so no.

They could try and move he seats from the duff Soyuz into Crew Dragon if they have suitable fixing points and tools...

It's still pressurised, so they should be able to survive in it.

But what happens when the capsule hits the water?

The same as those old-school wooden roller coasters with no safety-bar. You'd better hold on tight!

It floats. It’s designed for bringing cargo down as well as up, so it doesn’t experience dangerous levels of deceleration. Theoretically, it could bring back people, although they may need an oxygen tank and a way of strapping themselves down.

It probably wouldn’t be a pleasant landing, but it’s unlikely to injure them significantly.

And even if there is a significant injury risk, it's still better than certain death.