Difference for Logins and Users

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上进行时，仍然可能面临权限问题