User can't connect to Azure SQL Instance-CodePudding

I created my first database (let's call it MyDB) on Azure and created a user that I want to use to query the DB from my applications. I only wanted to give that user db_datawriter and db_datareader because I think that is all this user needs to do from the application.

When I run this on MyDB:

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;

I get this back:

db_accessadmin      No members
db_backupoperator   No members
db_datareader       MyDBUser
db_datawriter       MyDBUser
db_ddladmin         No members
db_denydatareader   No members
db_denydatawriter   No members
db_owner            dbo
db_securityadmin    No members
public              No members

so that seemed to work. The problem is - I have to give this user also Permission to connect to the DB in the first place, because when I try to connect to the server, I get this:

===================================

Cannot connect to abc.database.windows.net.

===================================

The server principal "MyDBUser" is not able to access the database "master" under the current security context.
Cannot open user default database. Login failed.
Login failed for user 'MyDBUser'. (.Net SqlClient Data Provider)

I think this is probably, because my new user can't connect to master DB, as the errormessage suggests. However, when I look at the properties of MyDB, "Connect" was granted for the user by dbo

How can I let my user connect to the Azure DB Instance, preferably with as less permission as possible, I would only like for him to select, update, insert etc. on MyDB

I tried to change default DB with this command, so I don't need to do anything on masterDB, but the stored procedure wasn't found:

Exec sp_defaultdb @loginame='MyDBUser', @defdb='MyDB'

Edit: That's how I created the Login / User in SSMS

First (on instance):

CREATE LOGIN MyDBUser
    WITH PASSWORD = '******' 
GO

Then (on MyDB):

CREATE USER MyDBUser
    FOR LOGIN MyDBUser
    WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_datawriter', N'MyDBUser'
GO

CodePudding user response：

You have created a LOGIN and then created a USER for the LOGIN but only in the database you want them to connect to but not in master.

SQL Azure Databases are contained databases, so there is no need to create a LOGIN; the databases use their own scoped credentials.

First DROP the USER and LOGIN you created. Connect to MyDB and DROP the user first:

DROP USER MyDBUser;

Then connect to master and DROP the LOGIN:

DROP LOGIN MyDBUser;

Now connect to MyDB again and CREATE the USER with the needed credentials:

CREATE USER MyDBUser WITH PASSWORD = N'Your Secure Password', DEFAULT_SCHEMA = N'dbo';

Then you can give it the needed database roles. Don't use sp_addrolemember; it has been deprecated for ~10 years.

ALTER ROLE db_datareader ADD MEMBER MyDBUser;
ALTER ROLE db_datawriter ADD MEMBER MyDBUser;

CodePudding user response：

I've had a similar error message when connecting to Azure SQL DB with SSMS. The solution there was to identify the database, hence the message "access the database "master"". You probably have entered the DB server name already.

You do not mention how you are trying to connect to the DB, but in case of SSMS, enter the name of the database on the tab "Connection properties" (available behind the "Options>>>" button on "Connect to Server" screen).