Grant Azure AD users read-only access to database views

I've got a SQL Server hosted in Azure that is connected to a data collection app and one of the reporting users needs access to a view on that database to do very basic reports in another application. What do I do?

Adding Azure AD Users to SQL Server Database

Step 1: Create a role for this reporting user

CREATE ROLE [ReportingViewReader]

Step 2: Grant access to the newly created role

GRANT SELECT ON ReportDataView TO ReportingViewReader

Step 3: Create your users

CREATE USER [john.doe@example.com] FROM EXTERNAL PROVIDER

When using Azure AD, it doesn't just add all of those users into your database or server. You have to create new users in your database and link them to an Azure AD account.

Step 4: Add user to the role

EXEC sp_addrolemember [ReportingViewReader], [john.doe@example.com]

Logging in with SSMS

Depending on your setup, these login steps might be a little different. In my instance, we will be logging in with Active Directory - Universal with MFA Support.

If you've added the permissions to a database and not to the specific server, then you will also need to click Options >>.

You can add the database you have access to here.

Once you click connect, you will be asked for your password, then sent a text message with your access code. Once you are connected, you will then be able to run select queries on your reporting view.