New user cannot login to SQL Azure

user_2608 0
Viewed 50913

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?

azure-sql-database
6 Answers

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. the blackened users are the users we added... and were like that

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.

user_13800550

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]
user_36046190

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
user_51690500
Related