I tried this syntax
CREATE USER username IDENTIFIED BY Password;
and it doesn't work.
This error is shown:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'BY'.
I don't know if the syntax is wrong or there's a proper and easier syntax in SQL Server for creating a user.
Documentation: CREATE USER
Here is one way to create a LOGIN and USER (these are two different things):
USE [master]
GO
CREATE LOGIN [newbiee] WITH PASSWORD=N'asdfl@##@$kljaio234234bb' MUST_CHANGE
, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [test]
GO
CREATE USER [newbiee] FOR LOGIN [newbiee] WITH DEFAULT_SCHEMA=[dbo]
GO
This is how you create a regular SQL Server user and login with permission to read, write and delete data:
USE [master]
CREATE LOGIN [username] WITH PASSWORD = N'long-password-123'
USE [database]
CREATE USER [username] FOR LOGIN [username]
GRANT SELECT, INSERT, UPDATE, DELETE TO [username]
