Search code examples
azure-active-directoryssmsactive-directory-groupazure-synapse

Grant Read access to Azure Active Directory Group in SQL DW (Synapse)

Can anybody help me with this, I'm stuck and reading didn't get me anywhere :(

My question is related to Azure Active Directory Group and SQL DW. I want to give database read access to an Azure Active Directory Group (ADD group). I took these steps for that:

  • I made two groups in ADD as Azure AnalytcisDW Admin Users and Azure AnalytcisDW Database Users, each with a bunch of users.
  • Then, I went to Azure Portal, selected my SQL DW and added Azure AnalytcisDW Admin Users group as Active Directory Admin (image below).
  • Then, I checked in SSMS and saw Azure AnalytcisDW Admin Users group is under mater=>Security=>Users (see image below)

Now I want to give Azure AnalytcisDW Database Users group the (database) read permission (using SSMS or anything). I couldn't figure out how. I read a bunch of stuff for it and couldn't find my way through, such as:

Grant Access to SQL Server Table to AD user

Adding Users to Azure SQL Databases

Active Directory Admin in SQL DW (Portal)

SSMS

Solution

  • I was able to do the same by using the below commands where db_users(same as your Azure AnalytcisDW Database Users) is the active directory group and I have used an id from db_admin (same as your Azure AnalytcisDW Admin Users) to connect to Synapse.

    enter image description here

    Ref doc : RoleAssign, AADGroupUser