Comparison Between Role of db_owner with Roles of db_datareader & db_datawriter in MS SQL Server

  1. db_owner role should only be given to the administrator who responsible for the database because this role can DROP the database
  2. db_datareader and db_datawriter is the minimum access that should be given to normal user to do the basic SELECT, UPDATE, INSERT and DELETE operation to the database
db_owner
 db_datareader
 db_datawriter
  1. Can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server
  2. Normal users should not be a member of this role.
  3. Some applications might require their user account to be a member of this role.
  1. Members of the db_datareader fixed database role can read all data from  all user tables.
  2. The db_datareader role gives implicit access to SELECT against all tables and views in a database.
  1. Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
  2. The db_datawriter role gives implicit access to INSERT, UPDATE, and DELETE against all tables and views in a database.

Source:

  1. https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15
  2. https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/