Although the terms login and user are often used interchangeably, they are very different.
* A login is used for user authentication
* A database user account is used for database access and permissions validation.
Login用来做用户登录验证，user用来控制对数据库的访问和权限验证
Logins are associated to users by the
security identifier (SID)
. A login is required for access to the Sql Server server. The process of verifying that a particular login is valid is
called "authentication". This login must be associated to a SQL Server
database user. You use the user account to control activities performed
in the database. If no user account exists in a database for a specific
login, the user that is using that login cannot access the database even
though the user may be able to connect to SQL Server. The single
exception to this situation is when the database contains the "guest"
user account. A login that does not have an associated user account is
mapped to the guest user. Conversely, if a database user exists but
there is no login associated, the user is not able to log into SQL
Server server.
Login和User可以进行映射，当然也可以不映射。这时，Login虽然能够登录，当有可能对任何数据库都没有访问权限。（有一点例外，如果数据库里有Guest用户，Login将默认使用Guest作为User）User虽然对数据库有访问权限，但由于首先缺少可以登录SQL Server的Login，实际上什么也做不了
When
a database is restored to a different server it contains a set of users
and permissions but there may not be any corresponding logins or the
logins may not be associated with the same users. This condition is
known as having "orphaned users."
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.
Login 是Sql Sever 实例级别的，User是数据库级别的
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.
即使是同一个Login Name, 在不同的server上，其SID可能是不同的（select sid,name from master.sys.syslogins），而Login和User 其实是通过SID来实现映射的，所以，即使使用相同的login，当数据库的备份和恢复是在不同的server上进行时，仍然可能面临权限问题
