Set database access with custom roles

Question

All contributors have complete access to the database and could drop the database.

We want to set the the access rights the following:

• contributors can access databases with the Entra-ID login. Database level roles / permissions include read/write/execute (procedures) 
• Dedicated users have database admin level role, enabling tasks required in deployment. This will also work with Entra-ID login, but activated only through PIM.

We have a custom database role that can execute procedures but is not able to drop a table. We have also a Azure role that can be activated via PIM.

What part do we have to set on the database level and what part do we set in Azure?

How can we put the things we have together?

Thank you for your advice and guidance

Answer 1

Good morning Roger Billeter

Base in my experience I'm going to make a little resume for you, must company do the same with admin users integrating with PIM.

Putting It All Together

For Contributors:

• Database Side:
  • Create Entra ID-based users and assign them to your custom database role (e.g., db_executor_limited) that lets them read, write, and execute procedures but prevents them from performing destructive actions like dropping tables.
• Azure Side:
  • In Azure, grant these contributors an Azure role that enables them to connect to the SQL Server resource with their Entra ID. This is your baseline access and is not controlled by PIM because their permissions are sufficiently restricted.

For Dedicated Users with Elevated Permissions:

• Database Side:
  • Have a higher‑privilege role (e.g., an admin role) defined but not active by default for these users.
• Azure/PIM Side:
  • Use PIM to manage the elevated Azure role assignment. Dedicated users are made eligible for a higher‑privilege role, but they must activate it via PIM when needed.
  • Once activated, their Entra ID login is then associated (or manually mapped) to the higher‑privilege database role, enabling necessary deployment tasks.

Automation & Governance:

• You can automate some of this mapping (for instance, via scheduled tasks or scripts) if your auditing and security policies require tight control over how and when such escalation happens.
• Combining these two layers ensures that everyday contributions remain safely within limited access boundaries while still providing a controlled pathway for administrators to perform critical tasks with the necessary permissions for a limited time.

😊 If my answer helped you resolve your issue, please consider marking it as the correct answer. This helps others in the community find solutions more easily. Thanks!

Answer 2

For the scenario :

• contributors can access databases with the Entra-ID login. Database level roles / permissions include read/write/execute (procedures) 

Create a DL and add all those users within that DL. Create a user for that DL via the below command

CREATE USER [DLName] FROM EXTERNAL PROVIDER;

And grant the below access to the DL :

EXEC sp_addrolemember 'db_datareader', 'DLName';

EXEC sp_addrolemember 'db_datawriter', 'DLName';

for SP, you can use Grant Execute

All the operations can be done within the database and no need of any aspect at Azure RBAC level.

• Dedicated users have database admin level role, enabling tasks required in deployment. This will also work with Entra-ID login, but activated only through PIM.

As long as the dedicated users have database admin role, there is no need of PIM . They can login directly to database via SSMS and do all aspects. So no need of PIM.

Hope this helps!!