6

This question already has an answer here:

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?

marked as duplicate by Tom V, Shanky, Community Dec 16 '15 at 9:50

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

4

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
...         ...             ...
  • These permissions are for a service account and db_owner is a prerequisite. But sometimes I see that for a SQL Login all three permissions are selected (db_datareader, db_datawriter and db_owner), actually when selecting db_owner, then db_datareader and db_datawriter should be grayed out because it's unnecessary to select those also – Ozkan Dec 16 '15 at 9:53
  • 1
    at the database level, db_owner is the highest you can go although you can still check any lower level roles such as db_datareader or over roles with rights already included in db_owner. – Julien Vavasseur Dec 16 '15 at 9:54
3

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.