0

I am trying to grant a Security Group within our company's Azure environment that is found in Azure AD. I have successfully granted users' rights to the db_datareader, writer, owner roles. But I am trying to add a Group to one of these roles and the error I get below.

I am an AD Admin of the SQL Server in Azure, and I confirmed that the Security Group is synced and found in our Azure AD environment. I have also confirmed running the commands below against the actual database and the master db.

I know that within Azure SQL you can run the T-SQL command to add a user by their UPN, this works fine:

CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datawriter] ADD MEMBER [bob@contoso.com];

But when running the below it provides an error

ALTER ROLE [db_datawriter] ADD MEMBER [groupname@contoso.com];

Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'groupname@contoso.com', because it does not exist or you do not have permission.

Do I also need to create a Group for the Sec Group as well? As in:

CREATE GROUP [groupname@contoso.com] FROM EXTERNAL PROVIDER; -- "GROUP" is not correct syntax.

or

CREATE USER [groupname@contoso.com] FROM EXTERNAL PROVIDER;

This article did not provide any details I could find on this topic but adding users.

5
  • contoso\groupname doesn't work for you?...that's how I've seen it usually.
    – J.D.
    Mar 10 at 20:15
  • Are you implying to attempt to run the command below? ALTER ROLE [db_datawriter] ADD MEMBER [contoso\groupname]; What I think is the issue, is that when you run any ALTER ROLE statement against Azure SQL. It can't see the AD User object in Azure AD until you create it with the CREATE USER .... FROM EXTERNAL PROVIDER Because when I ran any ALTER statement for individual users, it errored until I made them with that CREATE USER command. So I can attempt to run it, but I think I need a command along the lines like <CREATE GROUP ..... FROM EXTERNAL PROVIDER> Mar 10 at 20:28
  • But like I said, the "GROUP" is not correct syntax. It's weird, I can't find anything in MS Docs that cover this topic. They seem to only cover creating Users from Azure AD and not Groups. You can create Groups in SQL Server versions from AD just fine. But Azure SQL is different Mar 10 at 20:29
  • By the way, you need to make sure you add a Login the AD Group before you try mapping it's user to roles.
    – J.D.
    Mar 10 at 20:29
  • Could you elaborate or provide a link that explains about adding a Login for the AD Group? Mar 10 at 20:34
2

After more reviewing of this question, I was able to find that the syntax to add Security Groups in Azure SQL is below

CREATE USER [Group Display Name] FROM EXTERNAL PROVIDER;

You can confirm with one of Microsoft's articles

To create a contained database user representing an Azure AD or federated domain group, provide the display name of a security group:

CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;

And here is a second article that covers this topic. There is no CREATE GROUP syntax. To add a Sec Group it's done by using the CREATE USER syntax.

After which, you can proceed to run the ALTER ROLE command and use the display name of the Group to add the Group to the database role.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.