DENY overwrites GRANT, most of the time

Published On: 2009-09-21By:

SQL Server rights are pretty easy to work with most of the time.  You grant a bunch of rights to an object, then you deny rights to those objects and the user looses the rights.  Pretty easy.  Now the catch is that if you use the fixed database roles, those roles overwrite any denies that are in place.

So if you deny a user access to a bunch of tables, then you put that user into the db_datareader fixed database role that user will have select rights to all the tables in the database, including all the tables that the user has been denied access to.

Denny


Contact the Author | Contact DCAC

3 responses to “DENY overwrites GRANT, most of the time”

  1. BrianTkatch says:

    The topic is “DENY overwrites GRANT, most of the time”
    The post says “that user will have select rights ”

    Which was the point here?

  2. mrdenny says:

    They are both correct. When granting a user rights to an object by using the GRANT statement, any GRANT will be overwritten by a DENY. So if the user is in two domain groups, and both groups are defined in the database security, and one has been granted SELECT rights to a table, and one has DENY SELECT rights to a table the user will not have rights to the table.

    However if the user has been placed into the db_datareader fixed database role, this role overrides any DENY rights which have been granted to the user.

  3. BrianTkatch says:

    Ah, that explains it. thanx!

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link