db_datareader, db_datawriter and db_owner permissions [duplicate]

Question

First of all, sry because I am not a DBA and I'm not familiar with all the permissions.

My question is, if I give a user db_owner permissions on one database. Do I need to give also db_datareader and db_datawriter permissions to have full control? I think db_owner is sufficient to execute all kind of queries on the database? (create, delete, alter, select and all other stuff).

So, in short: If I choose db_owner, then db_datareader, db_datawriter are automatically included. Am I right?

Answer 1

According to Database-Level Roles:

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

A member of this Database level role can do whatever he wants on the database. This also include SELECT, INSERT, UPDATE and DELETE data.

You still have to make sure that you are not giving too much rights to a user when you make him a menber of db_owner...

You can connect with this user on your database and run this query to see effective Database level permissions using sys.fn_my_permissions:

SELECT *
FROM sys.fn_my_permissions(null, 'database')
--WHERE permission_name IN ('SELECT', 'UPDATE', 'DELETE', 'INSERT')
ORDER BY permission_name

Output with only db_owner includes following rows:

entity_name subentity_name  permission_name
...         ...             ...
database                    DELETE
database                    INSERT
database                    SELECT
database                    UPDATE
...         ...             ...

Answer 2

You dont need to to give db_datareader and db_datawriter permission if you have given db_owner permission. As per BOL

db_owner:

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

Even if you check the checkboxes for respective roles it won't have any affect