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 [email@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], [firstname.lastname@example.org]
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.