Use managed identities to connect to a Azure SQL database

Question

I have a question regarding the use of managed identities to connect to a SQL database. Do I need the ‘db_ddladmin’ role for this, or is it only necessary if I’m going to perform operations like CREATE, DROP, etc.? Additionally, if my managed identity is rotated unexpected by the platform, is there any mechanism that will assist me in recreating the USER or something similar so maybe we need the 'db_ddladmin' role? Thank you.

Answer 1

Hello Joe Chen,

Thanks for your question.

Depending on the full set of operations you want to perform, you can assign different roles based on least privilege.

The ‘db_ddladmin’ role is only required if the managed identity needs to perform operations that modify the schema of the database, such as CREATE, ALTER, or DROP. It is the least privileged if you need necessarily to perform ALL DDL operations.

These are documented here:

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

Per the managed Identity, SQL database access control is managed through database users and their assigned roles. the managed Identity Object ID will change but the SQL user will retain its database level roles.

Please let me know if you have further questions**

You can mark it 'Accept Answer' if this helped.