Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Difference between db_datareader and db_denydatawriter Expand / Collapse
Author
Message
Posted Thursday, January 13, 2011 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 8, 2012 7:04 AM
Points: 6, Visits: 44
Hi Everyone,

Please explain me the difference between db_datareader and db_denydatawriter.


Thanks & Regards,
Naveen
Post #1047110
Posted Thursday, January 13, 2011 3:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1047114
Posted Thursday, January 13, 2011 3:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 8, 2012 7:04 AM
Points: 6, 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
Post #1047121
Posted Thursday, January 13, 2011 3:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 1, 2014 7:35 PM
Points: 204, Visits: 1,013
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.
Post #1047128
Posted Thursday, January 13, 2011 3:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,283, Visits: 10,156
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1047129
Posted Thursday, January 13, 2011 3:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1047139
Posted Thursday, January 13, 2011 3:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1047143
Posted Thursday, January 13, 2011 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 8, 2012 7:04 AM
Points: 6, 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
Post #1047144
Posted Thursday, January 13, 2011 3:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 8, 2012 7:04 AM
Points: 6, Visits: 44
Thank you GilaMonster. I completely understood it now.
I appreciate your help

Naveen
Post #1047148
Posted Thursday, January 13, 2011 4:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1047153
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse