Usually Logins and Users are words which are interchangeable with each other. However in Microsoft SQL Server they are very different things. Because everyone assumes that they are the same thing, it can get a little confusing.
Logins are created at the database server instance level, while uses are created at the database level. In other words logins are used to allow a user to connect to the SQL service (also called an instance). You can have a login defined without having access to any databases on the server. In this case you would have a login, but no users defined. The user is created within the database and when it’s created is mapped to a login (users can be created without mapping them to a login, but we’ll talk about that at some point in the future). This mapping is what allows the person connecting to the instance to use resources within the database.
If the login was created directly within the database, each database would have to keep track of the usernames and passwords of everyone who needed access to the database, which would cause a security nightmare. Using the login in each database idea, lets create a login in each database called user1. We set the password for user1 the same on all the databases on the server. We then backup the database, change the password for that user on all the databases, then restore the database. We now have an out of sync password for a single database on the server.
Because of this mapping between logins and users, if you create a SQL Login on your server and grant it rights to a database via a user then backup the database, and restore the database to another server after creating a login on the second server with the same name. You would think that the login would have access to the database. However you would be wrong. This is because the SID of the login and the user are different. You have to use the sp_change_users_login procedure to sync the user with the login.
Denny
