How to create logins

Windows login

A login can be created by right-clicking on Security and selecting New Login or using:

CREATE LOGIN [AdventureWorks\Salespeople]
FROM WINDOWS
WITH DEFAULT_DATABASE=[tempdb];  -- optional, master is the default
GO

You can remove a login using DROP LOGIN, unless they’re logged in. If they are logged in, kill their session by finding their session ID in the SSMS Activity Monitor

SQL Server login

SQL Server logins are created for individual identities, in a similar way to Windows logins:

CREATE LOGIN SalesManager
WITH PASSWORD= ‘Pa$$w0rd’,
CHECK_POLICY=OFF; -- Windows password policies are enforced. The default value is ON.
GO

Password changing and Login expiry

Passwords can be reset using the GUI interface or via the ALTER LOGIN statement.

Database Users

A database user is a principal within a database, mapped to a server login.

CREATE USER Salespeople -- this doesn’t have to be the same as the server login
FOR LOGIN [AdventureWorks\Salespeople]; -- windows login
GO

or

CREATE USER SalesManager -- this doesn’t have to be the same as the server login
FOR LOGIN SalesManager;
GO

A database user cannot be disabled like a server login can. They can be altered (and retain the same SID) though, like so:

ALTER USER Peter WITH NAME = James;
GO