How Add Azure AD Groups in Azure SQL Database

Question

I want to add an azure AD group to sql server database, I was searching some helps in microsoft learn but I could only add individual AD users to databases. I would like create a group in Azure AD and add logins to this group to give access to different databases based on roles assigned to this groups.

I used the typical command to create login:

CREATE LOGIN [[email protected]] FROM EXTERNAL PROVIDER

after I added this login to user databases with:

CREATE USER [user]
    FOR LOGIN [[email protected]]
    WITH DEFAULT_SCHEMA = dbo
GO

I added this user to role that i wanted and the user has access to the database that I want, however for groups this process does not work because I received an error with permissions.

Answer 1

Use SQL Server Management Studio to get connected to Azure SQL Database using the Azure Active Directory Admin User and clicking New Query. Then execute the following statement:

CREATE USER [ADSQLGroup] FROM EXTERNAL PROVIDER

ADSQLGroup is the name of the group on Azure AD. You can create the group using Azure Portal and add members to it.

After that provide permissions on the database to the group.

EXEC sp_addrolemember 'db_datareader', 'ADSQLGroup'

You can find more information about providing access to AD Groups on Azure SQL here.