Can I provide non-admin users access to Azure SQL Server?

Question

Objective:

I am trying to configure a Azure SQL Server so that all users in an Azure Active Directory group can access the server and view/use any database on the server. Everyone will be using SSMS to connect to the server.

Attempted Solution & Problem:

I have been able to successfully grant access to a AAD Group only if I make the user group an admin of the server.

I want the AAD Group to be "Contributors" of the server. When I try this and then try connecting to Azure SQL Server, I get the following error:

enter image description here

Lastly, authentication will be based on "Azure Active Directory - Password" in SSMS.

Answer 1

Please make sure you create the group on Azure-SQL.

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;

Once you have the group created on the database, please make sure the SID of the group matches the SID on Azure AD. Please get the SID of the group on Azure SQL:

SELECT
    DP.name
    ,DP.principal_id
    ,DP.type
    ,DP.type_desc
    ,DP.SID
    ,OBJECTID = CONVERT(uniqueidentifier, DP.SID)
FROM SYS.database_principals DP
WHERE DP.type IN ('S','X','E')

Go to Azure AD and make sure is the same SID.

If they are not the same, drop the group and recreate it.