Here is a good article. You will have to register to read it, but registration is free.
Each login which is setup within the SQL Server has its own password. The login is what allows the user to see the databases. Logins are mapped to users within the database and rights within the database are granted to the users.
You can’t set a database specific password on the database like you can with Access.
This blog post covers the differences between Users and Logins.