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,

  • 1
    This is like "can eat chocolate" vs. "can't eat chips." Granting someone read access to a database is allowing them to read. That doesn't mean they can't write. And denying someone the right to write doesn't have any impact on their ability to read. The only time you would ever need an explicit deny data writer is if they inherit write privileges from elsewhere (like membership in a role). Typically if you don't grant someone the ability to write something, you don't have to take it away. – Aaron Bertrand Sep 26 '18 at 0:25
  • So if I intend to give access to an analyst who needs to query tables and not writing to them, my safest bet is db_denydatawriter, am I right? – Nico M. Sep 26 '18 at 17:34
  • Only if you think they will somehow get write privileges through some other path (like someone other than you adding them to a role with elevated privileges). It's principle of least privilege, not principle of always automatically denying all the privileges. :-) – Aaron Bertrand Sep 26 '18 at 19:59
  • Also the fixed database roles are really old. If you want a user to be able to run SELECT statements on a whole database, just GRANT SELECT TO THE_USER, for a single schema: GRANT SELECT ON SCHEMA::DBO TO THE_USER. – David Browne - Microsoft Nov 3 '18 at 15:32
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).

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

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.