New user cannot login to SQL Azure

I am creating a new read/write user on SQL Azure as follows:

-- Connected to master
create login [fred开发者_运维知识库] with password = 'xxx';

-- Connected to my DB
create user [fred] from login fred;
EXEC sp_addrolemember 'db_datareader', 'fred';
EXEC sp_addrolemember 'db_datawriter', 'fred';

When I login using SSMS I get an error saying Cannot open database "master" requested by the login. The login failed.

What am I doing wrong or missing?

---

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://learn.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]

---

Two other reasons that can trip you up:

1. The Server Login and the Database User must be the same. You cannot have APILogin link to APIUser, Azure just doesn't like it
2. Hyphens aren't allowed in usernames on Azure, so you can't have API-User