New user cannot login to SQL Azure

By default, SSMS tries to connect to master, but your new account does not have access to master; only the user database I presume.

On the login screen of SSMS, you need to specify the database name as well; just click on the Options >> icon, which opens up the Connection Properties tab. In there, specify the database name you are trying to connect to.

After creating the database user in the specific database Database1, again select 'master' and create database user. Execute below statement twice - one for Database1 and another for 'master'

CREATE USER appuser1 FROM LOGIN appuser1;

Unfortunately, this is not documented in Azure help https://docs.microsoft.com/en-gb/azure/azure-sql/database/logins-create-manage

--> Open new query window for master database and execute this commands

CREATE LOGIN AppLogin WITH password='XXXXXX'
GO

CREATE USER [AppUser] FOR LOGIN [AppLogin] WITH DEFAULT_SCHEMA=[dbo]
GO

--> Open new query window for YOUR Database

CREATE USER [AppUser] FOR LOGIN [AppLogin] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember 'db_owner', 'AppUser';
GO

Source: How to create custom user login for Azure SQL Database

As Karol commented in Herve Roggero's response, I had the same problem even after selecting the database.

In our case the problem was that the users we created in our databases were disabled.

After we run the following script in the database we wanted to connect for each user:

  GRANT CONNECT TO [ourDbUser]

We refreshed the database's users and now they were enabled, and then we were able to connect to the database successfully.

For me, the issue was that the person who created the user on the database did so without specifying FROM LOGIN, therefore the user was available in the Security->Users tab, but login was still not possible. I had to recreate the user and linking it to the login with the same name on the database:

DROP USER [myuser]
GO

CREATE USER [myuser] FROM LOGIN [myuser] WITH DEFAULT_SCHEMA=[dbo]
GO

and then granting the correct permissions on the database, in my case:

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