Azure SQL - User management with Active Directory Group

To manage the user's roles we use Service Account on OnPrem/IaaS servers. We can use this service account on our SQL Server and accordingly manage users' permissions for the users who are part of this service account.

Suppose we have an Azure SQL and we want to manage set-of-users permission shall we create each-and-every users' user profile in Azure SQL and set the permissions accordingly. Obviously, we will not. 

If we've anything like Service Account (or Group) on Azure we could create the user of this GROUP on Azure SQL and set its permission/role. Here comes the Azure Active Directory Group to help us. 

In case if our organization has some scheduled tasks to sync Active Directory (service account) to Azure Active Directory (AAD group), already in place, there will be some delay when we add a user to the Service account and get reflected in the AAD Group. If we have a requirement that as soon as we add a user to our group we want them to have access to our Azure SQL, Azure Active Directory GROUP is our solution. 

How to create the AAD Group. 

• Login to Azure Portal 
• Go to  your Azure Active Directory
• Create a new Group. 
• Once the AAD Group created add members to it. 

Now on Azure SQL

• Create an external user with this AAD GROUP, [make sure 
• Create a SQL role and assign it to this user on Azure SQL instance. 
• And we're good. 

Sample query add user/create-role/add-user-to-role/grant-permission

  -- Add user for your AAD Group
CREATE USER [your_add_group_name] FROM EXTERNAL PROVIDER;

-- Create role
CREATE ROLE [db_aad_group_member_role] AUTHORIZATION [dbo]

-- Add AAD Group user to this role 
ALTER ROLE db_aad_group_member_role ADD MEMBER [your_add_group_name];

-- Add grant permissions to this role
GRANT EXECUTE TO [db_aad_group_member_role]