Databases

Grant Azure AD users read-only access to database views

David Boothe

Jan 17, 2019 — 2 min read

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.

How to Delete a Git Commit From History

Have you ever committed something by accident and needed to remove the commit? Maybe you've committed something sensitive and you need to fully remove all history of it in your git repository. Well, this is where git rebase comes in to save the day. ⚠️WARNING: git rebase is

Configuring Git From A Fresh Install

When setting up a fresh install of Git, it's often a best practice to go an configure some basic settings like username and email. Setting The Username And Email In Git Globally The following command will setup your global username and email across all of your repositories. Just

Create a basic Admin Page layout using flexbox

In this tutorial we will go over some of the very basics in creating an Admin Page layout that includes various optional headers. For this example, we will be using Flexbox. Traditionally, aligning things vertically has been a challenge, but Flexbox makes it way easier. We will be going over

How to Prevent App Secrets From Ending up in Source Control for DotNet

Keeping secrets out of your git repositories is one of those lessons that people don't take seriously until it's too late. All the stories from people who learned this the hard way involve someone who hacked their cloud account and racked up charges anywhere from a