SQL Access Strategy

Question

I would like a strategy for the use of Azure SQL with our cloud environment.

The DB is in azure SQL. I would like to come up with a solution for giving access to developers and extended/other teams for dev/support needs.

WE have to add users and give access to the DB whenever they require and ask us. We go and manually add those users and allow them to work on DB,

Is there a better way to manage the work in Azure SQL?

• Should development be carried out on the Azure SQL Server directly?
  • How would an extended development team access the instance
  • How would we manage performance requirements
• Should we be using Synapse
  • would this make developer access easier?
  • How will we replicate data

Answer 1

We had a similar requirement, where it was tedious for us to maintain the users.

Developers - Need RW access to few set of tables

Analysts - R access on all tables

Others - R access to only few tables.

The initial setup was done manually.

We created several roles, each with its own set of rules for accessing tables and views.

We also created a few security groups and added these groups as users in Azure SQL using this command. This is in the link that @ShaktiSingh-MSFT provided.

CREATE USER [<Microsoft_Entra_principal_name>] FROM EXTERNAL PROVIDER;

When a new hire joins, you don't need to log in to the Azure SQL database to grant access. Instead you can add the user directly to the appropriate group.

If they don't need access, remove that particular person from the group, they cannot login to Azure SQL.

Answer 2

Hi Santhi Dhanuskodi •,

Kindly refer to the answer provided by Nikhil below.

Also, if you want to enable table level security measures, refer to Dynamic Data Masking and Row Level Security as described in below links:

https://learn.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview?view=azuresql

https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

Thanks.