1

I am trying to assign a SQL Server database role to a user who just needs to be able to see data across all tables.
I was wondering what is the difference between db_datareader and db_denydatawriter?
From the documentation I don't seem to realize any difference.

Thanks,

0
2

I am trying to assign a SQL Server database role to a user who just needs to be able to see data across all tables.

You should only make him a member of db_datareader.

By default, any newly created user has no permissions at all (unless you grant something to public or the corresponding login has some permissions at the server level: for example, view any definition at the server level implies view definition at database level once the login is mapped to a database).

1
  • This also includes system tables
    – clifton_h
    Sep 26 '18 at 16:22
1

I think a real-world example of when you would use the db_denydatawriter role will help.

One of my clients has a third-party vendor assisting with a long-term (2-year), fixed scope project. The vendor will be working on the client's domain during the project. For file sharing purposes, the vendor's AD users accounts were made part of an existing AD security group. We use that same AD security group to grant database access in SQL Server.

We don't want the vendor to be able to make any changes to the database, but we do want them to have the same read access as the other members of the AD security group.

We easily accomplished this by adding the individual vendor AD user accounts to the db_denydatawriter role.


A good rule of thumb is if you're not sure which role you need, you probably need db_datareader. The use cases for db_denydatawriter are far more specialized.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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