Difference between db_datareader and db_denydatawriter

Question

Post reply

Difference between db_datareader and db_denydatawriter

naveenkumar_segu

SSChasing Mays

Points: 600
More actions
January 13, 2011 at 3:14 am

#149650

Hi Everyone,
Please explain me the difference between db_datareader and db_denydatawriter.
Thanks & Regards,
Naveen

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

One grants select permissions on all tables

One denies insert, update and delete permissions on all tables

This should be explained in Books Online. Did you check there?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

naveenkumar_segu SSChasing Mays Points: 600 More actions · Answer 2

db_datareader -> grants select permissions and doesn't allow you to update anything in the tables (means denying insert, update and delete permissions)

db_denydatawriter -> also doesn't allow you to update anything in the tables (means denying insert, update and delete permissions)

I feel both roles do the same job. isn't it?

If so, what is the difference between them? I'm bit confused. I have searched in the internet and couln't get much help there.

However, thank you for your reply.

Naveen

naresh.talla SSCommitted Points: 1929 More actions · Answer 3

db_datareader you will able to read all tables where as in

db_denydatawriter you are not able to read any tables and does not allow to do insert,delete,update.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

At first sight they do indeed exactly the same.

I think it has something to do with how you implement your security model.

Are you giving everyone access first and then deny access to specific resources?

Or do you deny access first to everyone and then give specific access to some resources?

Those are two different ways of implementing security, so it seems logic to me that there are two different roles.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Koen (da-zero) (1/13/2011)
Or do you deny access first to everyone and then give specific access to some resources?

That won't work. Deny takes precedence over grant.

So if you make everyone a member of denydatawriter then grant some insert permissions, the people granted insert permissions will still not be able to insert, because the deny is more powerful.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

naveenkumar_segu (1/13/2011)
db_datareader -> grants select permissions and doesn't allow you to update anything in the tables (means denying insert, update and delete permissions)

db_datareader doesn't deny you insert, update, delete permissions. It simply doesn't grant them.

I feel both roles do the same job. isn't it?

No, not at all.

db_datareader grants select permissions on all tables. Nothing more. It does not affect any insert, update, delete permissions at all, if a user has those from somewhere else, they can insert, update and delete.

db_denydatawriter denies permission to do any changes to any table. Even if someone was granted insert permissions directly they would still not be able to insert, because deny overrules grant. Assigning a user to this role means that they will never be able to make any changes to the DB, regardless of what other permissions they have.

This does not grant select permissions, but it also does not deny select permissions.

Please do some reading on the differences between GRANT, REVOKE and DENY.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

naveenkumar_segu SSChasing Mays Points: 600 More actions · Answer 7

I agree with GilaMonster.

Still not getting the difference between those two roles. If both do the same job, why two then?

Sorry if I'm giving trouble to anyone. I appreciate your help.

Naveen

naveenkumar_segu SSChasing Mays Points: 600 More actions · Answer 8

Thank you GilaMonster. I completely understood it now.

I appreciate your help

Naveen

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

naveenkumar_segu (1/13/2011)
If both do the same job, why two then?

They don't do the same job. They do completely opposite things.

Let's say we have a database with three tables in it (t1, t2, t3) and a single user (U1)

I explicitly grant that user select permissions on t1 and insert permissions on t2

(grant select on t1 to u1; grant insert on t2 to u1)

Now he can read t1 and insert into t2. He cannot read t2 or t3, he cannot insert into t1 or t3.

Now I make the user a member of db_datareader. Now he can select on all tables (because db_datareader grants select on all tables), but can still only insert into t2.

Clear? The db_datareader adds extra select permissions, but does not touch insert, update or delete permissions at all.

Now I take the user out of db_datareader (so he just has select on t1 and insert on t2 again) and add him to the db_denydatawriter role.

The denydatawriter does not add or remove select permissions, so the user still has only select on t1. It however explicitly denies permission to make any changes, so the user can no longer insert into t2. He's been granted the permission, but also denied the permission and deny is stronger than grant.

So with select granted on t1, insert on t2 and denydatawriter the user can only select on t1 and nothing else.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

GilaMonster (1/13/2011)
Koen (da-zero) (1/13/2011)
Or do you deny access first to everyone and then give specific access to some resources?
That won't work. Deny takes precedence over grant.

Hmmm, you are absolutely right.

I believe I mixed it up with firewalls 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

naveenkumar_segu SSChasing Mays Points: 600 More actions · Answer 11

naveenkumar_segu

SSChasing Mays

Points: 600

January 13, 2011 at 4:12 am

#1272935

Thank you GilaMonster. Super explanation.

Naveen

quackhandle1975 SSChampion Points: 11055 More actions · Answer 12

Would I be right in thinking that when using both, the database would more or less be read only for that login?

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

csolervicens SSC Enthusiast Points: 184 More actions · Answer 13

Yes the concept is a bit confusing, so I'll try to explain the whole concept.

Any GRANT like gives a specific permission to someone.

Any REVOKE like deletes to someone a previously given permission (the permission must exists in order to be revoked)

DENY marks someone as a "undesirable" person (it's like to say "You will never be able to ..." ), so, if someone gives the denied permission to this person, the permission WON'T take effect, unless you remove the denial.

So DENY is a mark against a specific permission, and unlike revoke, the target (person or profile) don't need to have the permission in order to apply a DENIAL.

Hope it helps to understand the concept.

tchien69 SSC Enthusiast Points: 149 More actions · Answer 14

I think the key concept that Koen Verbeeck and probably many if not most people assume or miss (even after reading / skimming the docs) is that SQL Server Roles are not mutually exclusive. You can have multiple Roles. This is counterintuitive when compared (understandably but unfairly) to "jobs" / "positions" at employers. For most people at most employers, they can only be hired for / transferred to one position, so hiring (aka "grant"-ing) them one position automatically excludes them from other positions. Granted (pun-intended), if you compare SQL Server "Roles" with real-world "roles", it *is* a fair comparison, because even though you can usually only be hired / transferred to one "position" at most employers, you often can be assigned multiple "roles" - often outside your "job"'s / "position"'s description. For example, someone hired as / transferred to the "position" of Software Developer often has to serve the "roles" of BA, Architect, QA and Support Analyst as well.

If that were the case (and I suspect it was and, in my and I suspect most people's opinions, it was / would be obvious that it was), it would've saved a lot of typing and rounds of replies (and avoid sounding uppity which in my and I suspect most people's opinions, it did and would), if the responder simply said: "Unlike real-world roles (or more accurately, job positions), a SQL Server User can have multiple SQL Server Roles, so granting the "db_datareader" Role does not automatically negate the "db_denydatawriter" Role and vice versa.".