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?

score 151 · Accepted Answer · answered Jun 25 '11 at 22:08

151

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.

answered Jun 25 '11 at 22:08

Herve Roggero

5,149
1
17
11

5

I tried doing this, but I still get the same error. I had to create the user from the login on master as well. Like Reddy's answer below – kay Mar 10 '15 at 20:11
Thanks a ton Herve !! Solved my problem :) – Sana Jun 08 '16 at 16:42
1

A typo in the database name entered also results in the same error message – Karthikeyan May 17 '19 at 13:44
4

Sry to say but this "`By default, SSMS tries to connect to master`" is simply wrong! SSMS does not choose the default the database. If the connection string does not configures the database which you want to connect then THE SERVER will connect you to the default database. If you did not configure a default database for the user, then master is the default database by default. **In short**: choosing the default database is not related to SSMS but to the configuration of the user in the SQL Server – Ronen Ariely May 29 '19 at 16:34
Thanks, brother. It saves me time to search for alternate solutions. – Deependra Kushwah Jul 28 '20 at 11:42
Only clear the Password field on SSMS login, click on Options button and type the database name. Now you can return to Login tab, type the password and click on connect button. This works for me ! More information: https://azure.microsoft.com/nl-nl/blog/adding-users-to-your-sql-azure-database/ – Jesus Mostajo Jan 13 '22 at 08:14

score 50 · Answer 2 · edited Oct 06 '20 at 13:41

50

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

edited Oct 06 '20 at 13:41

Ian Kemp

28,293
19
112
138

answered Jul 13 '13 at 16:03

Jyo Reddy

712
5
10

6

This violates the [Contained user design](https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver15) that isolates a user to only the database they need – Red Jun 01 '20 at 12:53

score 21 · Answer 3 · answered Nov 11 '16 at 02:56

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

score 5 · Answer 4 · answered Oct 16 '17 at 19:38

5

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.

answered Oct 16 '17 at 19:38

D33

291
5
11

All that was missing for me as the GRANT CONNECT statement. Thanks. – jjthebig1 Oct 03 '21 at 15:35

score 4 · Answer 5 · answered Oct 11 '19 at 10:19

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]

score 1 · Answer 6 · answered Jun 01 '20 at 13:06

1

Two other reasons that can trip you up:

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

answered Jun 01 '20 at 13:06

Red

3,030
3
22
39

This one for me - It's 2022 and seems like Azure still requires the USER+LOGIN to be the same – Mark G Sep 26 '22 at 22:49

New user cannot login to SQL Azure

6 Answers6

Linked