It's not much about collisions, more like predictability of the output. You can trivially construct a rainbow table of the most common N passwords and test a dump of SHA-256 hashes against it. Also, SHA-256 is vulnerable to length extension attacks, so it may not be suitable in some applications on variable-length inputs.
Generally speaking, hashing user-provided data as-is is only a guarantee of integrity, not of authenticity (see HMAC), nor secrecy.