'SQL Server authentication and authorization case

I've got the following use case. We want to setup multiple SQL Server 2019 instances which multiple databases. But we're not sure how to setup the server logins and database users.

  • We will run a webapplication that can login to the sql server and to the specific customer database. So we're thinking about setting this up in a one to many config. So one login connected to many database users.
  • We've got multiple team members that need to access the databases to perform maintenance or analyse data. We don't want to reuse the database user that the webapplication uses. Instead we want to create temporary database users which are granted the needed crud permissions. These are revoked when the team member is done.

But we are unsure how to set this up and if this is a good and secure way of doing this. Thanks in advance!

Edit

But we are unsure how to set this up with the SQL server login account roles. Which server role does the SQL server login needs to have? Is the server role [public] enough?

sql-serverauthenticationauthorization


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions