Create login and user with roles in Microsoft SQL Server

I want to cover see how to create a login and associate that login with an user on a Microsoft SQL Server database. We will also see how to add database roles for the user.

Create login

Run the following query on the SQL Server instance:

CREATE LOGIN myuser
WITH PASSWORD = 'MyPassword!'

Create user

Run the following query in the database where you want to create the user associated with the login we created earlier:

CREATE USER [myuser]
FOR LOGIN [myuser]
WITH DEFAULT_SCHEMA = dbo;

Add roles to the user in the Database

Run the following query in the database where you created the user in the previous step, you can add a single role or multiple ones:

ALTER ROLE db_datareader ADD MEMBER [myuser];
ALTER ROLE db_datawriter ADD MEMBER [myuser];

-- you could also just give the db_owner role to the user for full access instead of specific ones:
-- ALTER ROLE db_owner ADD MEMBER [myuser];