SQL Server 2005 (Express) - Login vs User

Question

I'm quite new to Microsoft SQL Server. I have some experience with MySQL, and there you have a user with privileges, if I understand things right; these privileges decide which databases you have access to on the MySQL server.

However now I am in the situation where I have to restore a database on my SQL Server 2005 Express, and this database has it's own users and user password. So if I want to make these users accessible from the outside (so that they can connect to my server), how would I go about that?

To illustrate clearer; say there are two login accounts on the database server "Mike" and "John", and on the database "Animals" there are two users; "Chris" and "Jeff".

I need Jeff to be able to sign in to get access to the database. Is there a good way to make this happen without creating new users/logins? And if not, what is the best/most common solution?

I would really appreciate any helpful input on this!

Answer 1

One server-level object (login) is mapped to multiple database-level objects (users).

A login cannot be mapped to more than one user within a database, but can be mapped to at most one user in each database.

Therefore, you need to create new logins for those users, but map them to existing users. This is done with ALTER USER command. Or, if you don't have any use for the Mike and John logins apart from mapping them to those existing users, you can do so, too.

Answer 2

Any user needing to access a database needs to either have their own login, or you can create a login for a Windows security group and grant access that way to a whole set of users. Then if you need to give access to more users in the future you can just add them to the windows security group.