Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    8

    Question Unanswered: Users in Specific Roles and the db_owner Role

    Hello all, I did a search and couldn't find the answer to my question, so I'm hoping you guys can help me out.

    I'm reviewing the permissions for a specific table and came across a conflict that I don't know the answer to.

    Schema: dbo
    Table: Special
    Roles & Permissions: GroupA (Delete, Insert, Select, Update), GroupB (Select), User1 (Select)

    Now my first instinct is that based on this, the only people I need to be worried about are the users within GroupA. However, when I look at the Database Role Properties for db_owner, it says the following:

    Role Name: db_owner
    Owner: dbo
    Members of this role: [all the users from GroupA], [all the users from GroupB], [User1], dbo, [other users].

    So based on this information I'm inclined to think that they can do anything within any table in the database including the "Special" table I'm concerned with. Is this correct, or do the specific permissions to the "Special" table override the db_owner access?

    Thanks in advance for any help. Not sure why they changed this considering last year, the only member of the db_owner role last year was dbo.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You are correct. GRANT permissions are cumulative.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Posts
    8
    Quote Originally Posted by pootle flump View Post
    You are correct. GRANT permissions are cumulative.
    I'm sorry, but just to be clear (since I made a few statements), I'm correct in that all of the users have full access to the table even if they may not have been meant to?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes that is correct.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    GRANT permissions on a higher principal level only override REVOKE (neither GRANTed nor DENYed) permissions on a lower level. The exception is column-level explicitly GRANTed or DENYed permission, that overrides the explicit corresponding table-level permission. In your case if GroupA, GroupB, and User1 are not explicitly DENYed any permissions on your "Special" table, - they have full control over it, including structural modification control (DROP, TRUNCATE, ALTER).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Sep 2006
    Posts
    8
    Awesome. Thanks to both of you!! And yes, you're correct in that they don't have explicit DENY permissions to anything, only GRANT on the "Special" table.

    Thanks again!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •