In SQL Server, unlike Oracle, you have 2 sets of accesses to the database if I can say so:

• Login - Access to the server in general
• Username - Access to a specific database

A login can have access to a particular database or all databases, depending on its rights. Because of that, generally a DBA has to create a LOGIN for each user who wants to use the database and a username per specific database. The DBA can do that, either via the GUI (SSNS) or via scripts:

The creation of the database in MS SQL is straight forwards:

CREATE DATABASE [database_name]
ON ( NAME = N'database_name_data_01',
       FILENAME = N'D:\data\database_name\database_name_data_01.mdf',
        SIZE = 128MB, MAXSIZE = UNLIMITED , FILEGROWTH = 128MB )
     LOG ON ( NAME = N'database_name_log_01',
        FILENAME = N'D:\logs\database_name\database_name_log_01.ldf',
         SIZE = 128MB, MAXSIZE = 8192MB, FILEGROWTH = 128MB )
       COLLATE SQL_Latin1_General_CP1_CI_AS
; 

After a database is created, DBA can create a login and then username. Login is created on server level, where user is created on database level:

Login is single access to the server, usually it does not allow you access to any data. Login are used to ONLY connect you to the server.

CREATE LOGIN [tainops] 
	WITH PASSWORD=N'password', 
	DEFAULT_DATABASE=[database_name], 
	CHECK_EXPIRATION=OFF, 
	CHECK_POLICY=OFF
;
GO

After the Login is created, we can create a user associated with that login as follows:

USE [database_name] <- Again, users are database related, not server related.
GO
CREATE USER [username] FOR LOGIN [login_name]  <- Login name and username can be difference
GO
sp_addrolemember 'db_datareader','username' <- Role granting
GO
sp_addrolemember 'db_datawriter','username' <- Role granting
GO