What OpenID does is, in proper implementations, it allows us to sign in with any provider we choose. I could choose my own server as a provider — thus, it’s not necessarily “someone else’s web site”. And I don’t have to use passwords — I can use a password and a “security question”, I can use public-key cryptography, or I can hire a secretary to sit at the server in question and only authorize requests when she receives a phone call from me.
Even if we assume everyone continues to use the same password, with the same account, everywhere, it’s still better than a conventional login. With the conventional login, every site I log into could steal my password and use it to login as me elsewhere. With OpenID, only my OpenID provider can do that.
One single-point-of-failure is better than N single-point-of-failure.
Setting a good, complex password with your OpenID provider is at least as secure as using that same password at multiple sites (if they support it — various sites have various password rules).
The problem is, people choose bad passwords, but passwords aren’t going away anytime soon. I think there’s a slight chance of people choosing a stronger password if they know they don’t have to enter it all day long.