locked
Security Inheritance RRS feed

  • Question

  • Hello

     

    I am a bit confused with security inheritance (or if it even exists).  Example:  Using Windows Authentication SQL Server 2005, I am user Rob a member of the sysadmin role.  There is global group called GGStaff.  All staff including myself are members of GGStaff.  I have given GGStaff minimum permissions to SQL Server.  

     

    The thing that I find confusing is: I have set GGStaff deny permissions on an object but when I (Rob) use the object (insert/update) I have full permissions.  Is there some sort of permission inheritance that is happening (so if I’m a member of two groups do I get permissions from Group A and Group B)?

     

    I’ll try to explain further:

    When I connect to SQL Server does it:

    1. Log me on as Rob (sysadmin) exclusively
    2. Log me on as Rob then checks for whether GGStaff has specific permissions then merges the permission set together

     

    Please reply to me if this is too confusing and I’ll try to reword.

     

    Thank you

     

     

     

    Robert

    Wednesday, May 6, 2009 3:23 AM

Answers

  • Permissions are additive - unless there's a DENY somewhere. So, Tim, in this case, would have his own permissions and those assigned to the GGStaff login/user.
    Tibor Karaszi
    Thursday, May 7, 2009 6:55 AM

All replies

  • Robert,

    DENY should still take precedence. But what you see if s different artifact: for sysadmins there is no permission checks at all. This is why it is meaningless to DENY something from somebody who us sysadmin. You should be able to confirm this by ensuring that Rob isn't syadmin, but a more regular login (or using a different Windows login).
    Tibor Karaszi
    Wednesday, May 6, 2009 6:20 AM
  • Hello

    I should probably remove the sysadmin from the equation.  Just say user Tim has SELECT permissions granted on tblInvoices and he is also a member of GGStaff group/user (who doesn't have any permissions assigned to tblInvoices).  Does SQL Server use Tim's permissions or the GGStaff permissions?  Or does it merge the permission set?

    Thank you



    Robert
    Thursday, May 7, 2009 2:31 AM
  • Permissions are additive - unless there's a DENY somewhere. So, Tim, in this case, would have his own permissions and those assigned to the GGStaff login/user.
    Tibor Karaszi
    Thursday, May 7, 2009 6:55 AM
  • I see, I wish this information was listed in the MSPress MCITP/MCTS SQL Server 2005 books!

    Thank you
    Thursday, May 7, 2009 8:18 AM