Azure SQL is not letting me create a SQL User

Question

I have a Database server created in Azure [not a managed instance] and a Server Admin user was created, many SQL users were added and they were able to login without any issues. Now all of a sudden, SQL users I newly create can't login. It's giving Login failed message with Error# 18456

Here are the SQL commands I am using to create the user [after logging in as Server Admin]:

1. CREATE LOGIN reportslogin WITH password='' - this in "master" database
2. CREATE USER reportsuser from LOGIN reportslogin - this is in MyDatabase
3. ALTER ROLE db_datareader ADD MEMBER reportsuser - this again is in MyDatabase

Now I disconnect the server and when I try reconnecting to the server using the reportsuser, I get this:

What am I doing wrong?

Answer 1

Actually, the error is caused by you database login name.

You should using the Login reportslogin to login the database, not the username reportsuser !

We must using the login name to access the Azure SQL database, not the username.

A login is a simple credential for accessing SQL Server. For example,

SQL Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level.

Please reference:

1. Login and SQL User
2. Difference between a User and a Login in SQL Server

Suggestions:

1. create the login and user with the same name:

   USE master CREATE LOGIN reportslogin WITH password='Xunmi110'

   USE user_database CREATE USER reportsuser FOR LOGIN reportslogin ALTER ROLE db_datareader ADD MEMBER reportsuser

2. One login for one user.

Hope this helps.