Lesson Learned #23: How to add AAD Security Groups to Azure SQL Database?

發行項
03/10/2017

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 - /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