In these days, we are receiving multiples cases where our customer needs to add Azure SQL Database groups from Azure Active Directory.
This is an example to how to archive it.
- Using the Portal in Azure:
- I created a user called SQLMember.
- I created a group called SQLGroup, adding the member SQLMember.
- Using SQL SERVER Management Studio connected to the User Database using the Azure Active Directory Admin User and clicking New Query.
- I create as user group of this database – CREATE USER [SQLGroup] FROM EXTERNAL PROVIDER – https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins
- Running SELECT * FROM SYS.DATABASE_PRINCIPALS we could see that the group has been created in Azure SQL Database
- I executed the TSQL: EXEC sp_addrolemember ‘db_datareader’, ‘SQLGroup’ to give the permissions to this group just of read-only. – https://msdn.microsoft.com/en-us/library/ms187750.aspx
- Finally, I disconnected from SQL Server Management Studio and I connect again using the user SQLMember@mydomain.com to the database and I was able to connect without issue, applying the read-only permissions for the database