SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between db_datareader and db_denydatawriter


Difference between db_datareader and db_denydatawriter

Author
Message
naveenkumar_segu
naveenkumar_segu
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 44
Hi Everyone,

Please explain me the difference between db_datareader and db_denydatawriter.


Thanks & Regards,
Naveen
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228135 Visits: 46339
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
naveenkumar_segu
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 44
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
naresh.talla
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 1015
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
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63494 Visits: 13298
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228135 Visits: 46339
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228135 Visits: 46339
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
naveenkumar_segu
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 44
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
naveenkumar_segu
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 44
Thank you GilaMonster. I completely understood it now.
I appreciate your help

Naveen
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228135 Visits: 46339
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search