CODEWITHSUNDEEP
sql-server
Newbiee
Newbiee

Reputation: 43

Creating User and Password in SQL Server

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.

Upvotes: 2

Views: 10158

Answers (2)

Christian Davén
Christian Davén

Reputation: 18127

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]

Upvotes: 0

Brien Foss
Brien Foss

Reputation: 3367

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

Upvotes: 7

Related Questions