New user cannot login to SQL Azure

Question

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?

Answer 1

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.

Answer 2

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