Hey there! That error you're hitting is really common when you're used to syntax from other databases (like MySQL) and try applying it to SQL Server. Let's get this sorted out quickly.

Why the Error Happens #

SQL Server doesn't use the IDENTIFIED BY clause to set user passwords—this syntax belongs to databases like MySQL or Oracle. When you include it in your CREATE USER statement, SQL Server gets confused, hence the "Incorrect syntax near 'BY'" error.

Correct SQL Server Syntax for Creating Users #

SQL Server separates server-level logins (which let you connect to the SQL Server instance) and database-level users (which grant access to specific databases). Here's how to do it properly:

1. Create a Server Login (Most Common Scenario)

First, create a login that can access the SQL Server instance:

CREATE LOGIN [MyNewLogin] WITH PASSWORD = 'MySecurePassword123!';

Note: SQL Server enforces password complexity by default—make sure your password includes uppercase letters, lowercase letters, numbers, and special characters.

Then, map that login to a user in your target database:

USE MyTargetDatabase;
CREATE USER [MyNewUser] FOR LOGIN [MyNewLogin];

2. Create a Contained Database User (No Server Login Needed)

If you're using a contained database (supported in SQL Server 2012+), you can create a user directly in the database without a server-level login:

USE MyContainedDatabase;
CREATE USER [MyNewUser] WITH PASSWORD = 'MySecurePassword123!';

Example of the Wrong Syntax (That Causes Your Error) #

Just to clarify, this is what you might have tried (which works in MySQL but not SQL Server):

CREATE USER MyUser IDENTIFIED BY 'MyPassword';

This is exactly what triggers the "Incorrect syntax near 'BY'" message—SQL Server has no idea what to do with the IDENTIFIED BY part.

内容的提问来源于stack exchange，提问作者Newbiee