Assign Azure SQL database permissions to AAD group

Intro
When you create a Azure SQL database (DB) right of the bat, you will be faced with the need to assign permission in the database to users or security groups. Normally on a on-prem SQL DB it’s no problem and can be done using the GUI in SSMS. But for a Azure SQL DB, there is no GUI to assign permissions, you you will need to use SQL querys to assign permissions to users or groups. Let me show you how to assign SQL DB permissions to a AAD security group.

Assigning the permissions

Prerequisites:
* Azure Active Directory (AAD) Security Groups you want to assign permissions to
* Access to a AAD Azure SQL admin account

Note: This also works with on-prem AD security groups (in a hybrid envoriment)

The topology that I will be using is one AAD group to Grant Logon permissions to the SQL server, and then add three groups to that group in AAD , and then afterwards I’ll assign the same three groups the following permission set: Owner, Writer & Reader.
The reason for this topology is that I want to save time, when I eventually have to add another database to the SQL server. When the time comes i can just create three AAD groups and add them to the “ACL_GrantLogin_xx” group and not have the need to assign permissions in the master DB, and then just set the permissions on the database i just created. For me the administration is easier in the long run and more transparent.
The other reason is ‘cleanness’ in the Master DB, in ‘only’ having one group and not serval groups.

This the groups i’m going to ad:
ACL_GrantLogin_vFrohnSQL (Used in the master DB)
– ACL_DBOwner_MyDatabase01
– ACL_DBWriter_MyDatabase01
– ACL_DBReader_MyDatabase01

To assign database permissions to a ADD group, you need to connect to the SQL server using SQL Server Management Studio with a AAD account with admin rights

First of you will need select the database in GUI and then select “New query” – Normally on a on-prem SQL server, you can use the “Use” statement, but that is currently not support on Azure SQL – But you can upvote on UserVoice here.

Query on the Master Database
Start by selecting “New query” and then select “master” in the drop down and type in the following:

CREATE USER [ACL_GrantLogin_vFrohnSQL] FROM EXTERNAL PROVIDER

Then hit “Execute (F5)”

Query on the Database (Mydatabase01)
Then Select your database in the drop down menu – Mine is named “MyDatabase01”, then Select “New query” and type the following:

Then hit “Execute (F5)”

Confirmation
You can check that it worked by running this command:
SELECT * FROM SYS.DATABASE_PRINCIPALS

That is it.
You can now add users to the ADD groups and have them login using SSMS and the Azure Active Directory functions. You find the querys on my github page – https://github.com/vFrohn/AzureSQL