best-practice recommendation for SQL DB access for the Devs and DevOps teams

Hi,

There are some best practices rules you can follow which is a balance between security, ease of use and operational efficiency.

• Principle of least privilege
• Use Entra ID accounts for authentication so conditional access settings can be enforced (f.e. MFA)
• Use role-based access control for database users (db_datareader, db_datawriter, db_ddladmin)
• Separate database environments but I think this is something you have
• Monitor, audit and review access
• Use deployment pipelines

In summary something like that but really depends on the company policies, culture and maturity of the company.

Development Environment:

Developers: Full access.
DevOps: Full access. 

Pre-Prod Environment:

Developers: Read-only access.
DevOps: Schema and pipeline deployment access

Production Environment:

Developers: No access (use logs, reports).
DevOps: Automated deployment via service principal