What is the difference between a login and a user in SQL Server security?

In SQL Server security, a login is a principal that allows access to the SQL Server instance as a whole, while a user is a principal that allows access to a specific database within the instance.

A login is used to authenticate a user’s connection to the SQL Server instance. It consists of a set of credentials, such as a username and password, or a Windows account, that are validated by the SQL Server instance.

On the other hand, a user is a database-level principal that has a particular set of permissions within a database. A user is created based on the login used to connect to the SQL Server instance. Each login can be associated with one or more database users, and each user is associated with only one login.

Here is an example of creating a login and a user:

CREATE LOGIN [testlogin] WITH PASSWORD=N'testpass'
GO
USE [testdb]
GO
CREATE USER [testuser] FOR LOGIN [testlogin]
GO

In this example, the ‘CREATE LOGIN‘ statement creates a login with the name ‘testlogin‘ and a password of ‘testpass‘, and the ‘CREATE USER‘ statement creates a database user with the name ‘testuser‘ for that login in the ‘testdb‘ database.

It is important to note that a login can have access to multiple databases within the SQL Server instance, but a user only has access to the specific database it is created in.

In summary, a login is used to authenticate access to the SQL Server instance, while a user is used to grant access to a specific database within the instance.