SQL Server Interview Question #181

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

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

A login is a server-level security principal used to authenticate or identify access to a SQL Server instance. A database user is a database-level principal that provides access inside a specific database.

A login can be mapped to users in multiple databases. Permissions should normally be assigned through database roles rather than individually wherever practical, following the principle of least privilege.

Code Example

CREATE LOGIN AppLogin WITH PASSWORD = 'Use-A-Strong-Secret-Here';
GO
USE SalesDb;
CREATE USER AppUser FOR LOGIN AppLogin;
ALTER ROLE db_datareader ADD MEMBER AppUser;