Azure SQL - User Management With Azure Active Directory Group

In this blog, you will learn about Azure SQL user management with Azure Active Directory Group.

Introduction 

 

To manage user 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 a 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 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 an 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 you log in to Azure SQL with AAD your AAD account)
• Create a SQL role and assign it to this user on Azure SQL instance.
• And we're good

Sample query to 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]