Hi folks! I'm currently setting up Azure SQL Server and databases and need some help with permissions. I've designated an Entra ID group as the admin for my SQL Server and created five databases inside it using Terraform. Now, I want to give access to another Entra ID group that isn't the admin group to these databases.
My main questions are:
1. Can I do this through Azure IAM/RBAC?
2. I want to automate the access configuration with Terraform, but my Terraform service principal isn't part of the admin group. I checked the Azure RBAC roles, but none seem to suit database-level access. Also, just to make sure I'm clear, being an admin of the SQL Server doesn't automatically grant access to the individual databases, correct? Any suggestions would be really helpful!
2 Answers
You might consider using a bacpac file stored in blob storage for your database setup, which can help manage permissions. Alternatively, using an automation runbook that connects to the database and adjusts permissions could work too—just make sure the automation's managed identity is part of the admin group.
You can't manage database-level permissions in Azure SQL using RBAC or Terraform. Instead, you have to do this using T-SQL commands, which can be triggered through PowerShell if needed. Also, yes, while the server admin has full access to all databases, remember that the 'owner' role in RBAC is tied to just the server resource itself, not the databases within it. Just keep that in mind!
Thanks for clarifying! So, if I want my Terraform service principal to execute those T-SQL commands, I'd need to give it admin rights on the SQL Server, right? Seems limiting having only one admin group!
This sounds like a solid plan! Keeping your database schema and security settings under source control and deploying via CI/CD is definitely a best practice.