When you create an Azure SQL database (DB) right off the bat, you will be faced with the need to assign permissions in the database to users or security groups. Normally on an on-premises SQL DB it’s no problem and can be done using the GUI in SSMS. But for an Azure SQL DB, there is no GUI to assign permissions, you will need to use SQL queries to assign permissions to users or groups. Let me show you how to assign SQL DB permissions to a Microsoft Entra ID (formerly Azure Active Directory/AAD) security group.
Last Updated: November 2, 2025
Prerequisites
- Microsoft Entra ID Security Groups you want to assign permissions to
- Access to a Microsoft Entra ID Azure SQL admin account
- Minimum permissions: ALTER ANY USER permission, or db_owner role membership, or be the Entra ID admin
Note: This also works with on-prem AD security groups (in a hybrid environment)
Assigning the permissions
The topology that I will be using is one Microsoft Entra ID group to grant login permissions to the SQL server, and then add three groups to that group in Entra ID, 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 Entra ID 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 several groups.
These are the groups I’m going to add:
- ACL_GrantLogin_CFP-SQLServer (Used in the master DB)
- ACL_DBOwner_MyDatabase01
- ACL_DBWriter_MyDatabase01
- ACL_DBReader_MyDatabase01
To assign database permissions to a Microsoft Entra ID group, you need to connect to the SQL server using SQL Server Management Studio with a Microsoft Entra ID account with admin rights.
First you will need to select the database in the GUI and then select “New query”.
Query on the Master Database
Start by selecting “New query” and then select “master” in the drop-down menu and type in the following:
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:
Microsoft Entra ID users are marked in the database metadata with:
- Type E (EXTERNAL_USER) for individual users
- Type X (EXTERNAL_GROUPS) for groups