Home
%3CLINGO-SUB%20id%3D%22lingo-sub-368866%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2323%3A%20How%20to%20add%20AAD%20Security%20Groups%20to%20Azure%20SQL%20Database%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368866%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Mar%2010%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20In%20these%20days%2C%20we%20are%20receiving%20multiples%20cases%20where%20our%20customer%20needs%20to%20add%20Azure%20SQL%20Database%20groups%20from%20Azure%20Active%20Directory.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20This%20is%20an%20example%20to%20how%20to%20archive%20it.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EUsing%20the%20Portal%20in%20Azure%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EI%20created%20a%20user%20called%20SQLMember.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EI%20created%20a%20group%20called%20SQLGroup%2C%20adding%20the%20member%20SQLMember.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EUsing%20SQL%20SERVER%20Management%20Studio%20connected%20to%20the%20User%20Database%20using%20the%20Azure%20Active%20Directory%20Admin%20User%20and%20clicking%20New%20Query.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EI%20create%20as%20user%20group%20of%20this%20database%20-%20CREATE%20USER%20%5BSQLGroup%5D%20FROM%20EXTERNAL%20PROVIDER%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-manage-logins%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-manage-logins%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ERunning%20SELECT%20*%20FROM%20SYS.DATABASE_PRINCIPALS%20we%20could%20see%20that%20the%20group%20has%20been%20created%20in%20Azure%20SQL%20Database%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EI%20executed%20the%20TSQL%3A%20EXEC%20sp_addrolemember%20'db_datareader'%2C%20'SQLGroup'%20to%20give%20the%20permissions%20to%20this%20group%20just%20of%20read-only.%20-%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms187750.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms187750.aspx%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EFinally%2C%20I%20disconnected%20from%20SQL%20Server%20Management%20Studio%20and%20I%20connect%20again%20using%20the%20user%20SQLMember%40mydomain.com%20to%20the%20database%20and%20I%20was%20able%20to%20connect%20without%20issue%2C%20applying%20the%20read-only%20permissions%20for%20the%20database%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368866%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Mar%2010%2C%202017%20In%20these%20days%2C%20we%20are%20receiving%20multiples%20cases%20where%20our%20customer%20needs%20to%20add%20Azure%20SQL%20Database%20groups%20from%20Azure%20Active%20Directory.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368866%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eaad%20security%20groups%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20DB%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edb_datareader%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eselect%20from%20sys%20database_principals%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esp_addrolemember%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190995%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2323%3A%20How%20to%20add%20AAD%20Security%20Groups%20to%20Azure%20SQL%20Database%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190995%22%20slang%3D%22en-US%22%3E%3CP%3EDid%20you%20create%20the%20user%20in%20Azure%20Active%20Directory%20or%20SQL%3F%3CBR%20%2F%3ESame%20for%20the%20Group%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
First published on MSDN on Mar 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.








  • Running SELECT * FROM SYS.DATABASE_PRINCIPALS we could see that the group has been created in Azure SQL Database






  • 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

1 Comment
New Contributor

Did you create the user in Azure Active Directory or SQL?
Same for the Group?