CODEWITHSUNDEEP
securitysql-server-2008-r2
Aaron Anodide
Aaron Anodide

Reputation: 17196

What is the difference between being db_owner vs. db_datareader / db_datawriter?

I developed a business application at essentially the same time I learned to do non-trivial anything with SQL Server. I give data entry people db_owner and viewers db_datareader.

Now I'm trying to harden things up and the logical thing to my mind is give db_datareader and db_datawriter instread of db_owner, but I'm curious about (a) is this the right thing to do? and (b) what kinds of things can db_owner do that data_writer can't? (i.e. will anything not work after I switch).

Upvotes: 13

Views: 20586

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database. db_datawriter can perform DELETE, INSERT, UPDATE while db_datareader can only perform SELECT operations.

You should always try to grant only the minimum level of permissions needed to accomplish tasks. Most users don't need access to configuration or management features. In the case of data entry users, db_datawriter is probably appropriate.

Upvotes: 13

Related Questions