adding new users to Azure database Microsoft SQL Server Error: 18456)

Question

We have a couple of users that would like access to the Azure data warehouse but after creating the users in the database (using Azure active directory for authentication) they are getting an authorization error. (Microsoft SQL Server Error: 18456)

We added the users as contributors to the Azure SQL Server and the users were created using the external provider option

CREATE USER [email] FROM EXTERNAL PROVIDER;

We are new at this so I think we missed a step somewhere but we are not sure where. Thanks in advance for any assistance.

Answer 1

Hi Vineyard, how are you?

It seems that is missing the step to add permissions over the database, after creating the contained user. You can see below example commands:

EXEC sp_addrolemember N'db_datareader', <user>'

EXEC sp_addrolemember N'db_datawriter, <user>'

I hope this can help.

Greetings.