0

When provisioning a new Azure Active Directory user's access to an Azure SQL Data Warehouse or Database does the user need to be added to the master database in the Azure SQL Server?

This documentation only talks about adding the Azure Active directory user to the specific warehouse database and associating them with a role within that database. I have however found that unless the user is added also added to the master database then they cannot sign on via SSMS.

Here is what I am doing:

CREATE USER [joe@domain.com]
from external provider
WITH DEFAULT_SCHEMA = dbo

EXEC sp_addrolemember 'db_datareader', 'joe@domain.com'

--toggle to master
CREATE USER [joe@domain.com]
from external provider
WITH DEFAULT_SCHEMA = dbo

Does the user always need to be added to master? Is there a better way to configure security so that I do not always have to add the user to master? Am I totally missing something here?

codechurn
  • 3,870
  • 4
  • 45
  • 65
  • Did you try specify the database when you connect to the database with SSMS? – Leon Yue May 20 '20 at 04:46
  • @LeonYue I am assuming you mean by going to options and typing the name; is this how it is supposed to work? If the user is in the _master_ database then they do not have to do this and can see all of the databases hosted the Azure SQL Server. I know in the traditional on-premise SQL Server it was generally bad practice to do anything with the _master_ database – codechurn May 20 '20 at 13:30

0 Answers0