Azure RBAC for SQL Server Access Control

Hi Nalini Bhavaraju,
It sounds like you're looking to configure Azure RBAC for SQL Server to meet some specific access requirements for your databases. Here's a general approach you can take to achieve this:

Viewing Access: To allow users to view all databases/resources under the SQL Server, you can assign them the SQL Server Reader role at the SQL Server level. This gives them the ability to view resources without making any changes.

Specific Database Permissions:

For the first database, where users need to create and update tables/views/schemas but cannot delete them, you will need to use a combination of database-level roles. Assign them the db_datawriter role to allow them to create and update tables and views, and do not assign the db_owner role to prevent them from deleting any objects.

To ensure users cannot delete any database objects, you'll have to manage this through permissions set at the database level instead of RBAC. You might also want to consider row-level security depending on how granular you want the control to be.

Read-Only Access: For the second database, assign the users the db_datareader role, which allows them to read data without making any changes.

Preventing Deletions and Role Management: Avoid using roles like SQL DB Contributor or Contributor, as they allow for deletions. By applying the specific database roles mentioned, you should prevent users from deleting databases, tables, schemas, etc.

Creating Custom Roles: If needed, you can create a custom role that includes only the permissions necessary for your users, such as allowing create and update access without delete permissions.

Here's a quick reference for the roles:

• SQL Server Reader: Allows viewing of SQL Server resources.
• db_datareader: Allows read access to all tables in the database.
• db_datawriter: Allows insert, update, and delete access to all tables in the database (the users will need a custom role if deletion is to be restricted).

I recommend reviewing more detailed permissions and role settings in Azure documentation related to database access control to tailor it precisely to your needs.

Hope this helps! If you have any more questions or need further clarification, feel free to ask!