locked
70-432 and database roles RRS feed

  • Question

  • I am studying the book "Microsoft SQL Server 2008 - Implementation and Maintenance" by Mike Hotek and am confused by a point.  On p. 270 the Lesson Summary states "Member of the db_owner role can perform any action within the given database and cannot be prevented from executing any command withing the database."   This seems incorrect as I have used the DENY CREATE TABLE TO username command on members of the db_owner role.  After execution, they cannot create tables.  I have tried this on both SS 2005 & 2008.

    So, if this question comes up on the exam how do I answer?  According to the book or according to my own experience?  Mikes book is so spot on that I cannot help but think I am interpreting something incorrectly.  

    Sunday, April 28, 2013 10:24 PM

Answers

  • You should not believe blindly everything that you read on the books. It's not uncommon to find an occasional error even in books that are otherwise excellent. Even the official product documentation contains mistakes.

    If the question comes up in the exam, just remember that DENY takes precedence over GRANT. If a user belongs to a role that has a DENY permission for some operation, or if a DENY is directly assigned to a user, the operation will be denied even if the user has the permission granted directly or through membership in some other role. And as you already found out, this can even affect users who are members of a privileged role such as db_owner.

    • Marked as answer by MrAndrewwayne Monday, April 29, 2013 2:03 PM
    Monday, April 29, 2013 7:12 AM

All replies

  • You should not believe blindly everything that you read on the books. It's not uncommon to find an occasional error even in books that are otherwise excellent. Even the official product documentation contains mistakes.

    If the question comes up in the exam, just remember that DENY takes precedence over GRANT. If a user belongs to a role that has a DENY permission for some operation, or if a DENY is directly assigned to a user, the operation will be denied even if the user has the permission granted directly or through membership in some other role. And as you already found out, this can even affect users who are members of a privileged role such as db_owner.

    • Marked as answer by MrAndrewwayne Monday, April 29, 2013 2:03 PM
    Monday, April 29, 2013 7:12 AM
  • Alberto is correct here. When I was first learning about permissions, I was told by an instructor the granting permissions may allow someone to do something, but Deny absolutely means deny. FYI, Microsoft Learning has recently created online certification study groups. I am helping to moderate the database study groups. They were specifically created to help those studying for the SQL 2012 exams but there is a lot of overlap between the SQL 2008 and SQL 2012 exams. You might find it helpful to participate in those study groups. Here is the URL: http://borntolearn.mslearn.net/certification/database/default.aspx#fbid=ENYH-O4RlGB

    Good luck on your exams. Please post when you take your exam to let us know how you did.


    Mike Corkery, MCT, MVP (Office Systems), MCSD (Windows Store Apps), MCITP, MCPD, MSF, etc. Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, April 29, 2013 1:55 PM
  • Thanks for the reply Alberto.  I was just a bit thrown off because in the next section he later expands on the concept that the sysadmin and db_owner roles are not affected by DENY.   I guess it's good I took the time to test that out!

    Thanks again.

    Monday, April 29, 2013 2:07 PM