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,
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).
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.
