What is Difference between Logins and Users.
Logins and Users are complete different things in Microsoft SQL Server. Many of us assumes that they are the same thing, it can get a little confusing. The basic difference is login is created on instance level and when a login is given access to a particular database then it's call a user for that database.
Logins are created at the database server instance level, while uses are created at the database level.
CREATE LOGIN TestLogin WITH PASSWORD = 'I4india@2012'
select * from sys.syslogins where name='TestLogin'
CREATE USER TestUser FOR LOGIN TestLogin
select * from sysusers where name='TestUser'
There are two main categories of logins: SQL Server authenticated logins and Windows authenticated logins. I will usually refer to these using the shorter names of SQL logins and Windows logins. Windows authenticated logins can either be logins mapped to Windows users or logins mapped to Windows groups. So, to be able to connect to the server.
Access to databases is granted via users.
Users are mapped to logins and the mapping is expressed by the SID property of logins and users. A login maps to a user in a database if their SID values are identical. Depending on the type of login, we can therefore have a categorization of users that mimics the above categorization for logins; so, we have SQL users and Windows users and the latter category consists of users mapped to Windows user logins and of users mapped to Windows group logins.