Home Forums SQL Server 2005 Administering Difference between db_datareader and db_denydatawriter RE: Difference between db_datareader and db_denydatawriter

  • 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