locked
How permissions work for user who is a member in 2 Active Directory Groups RRS feed

  • Question

  • Hello folks,

    My id is the member of AD group.

    I was missing exec permission on 1 procedure. When I requested exec permission on the procedure, i've been said that this permission is granted to another AD group.

    After my id was added to another AD group, I still do not have permission to execute this procedure. Do you think my id should be removed from group 1 and added to group 2 instead just added to group 2?

    I suspect sql security chooses most restrictive out of 2 permission sets.

    What do you think?


    gene

    Friday, March 9, 2012 8:30 PM

Answers

  • The permission for an action on an object granted to a principal (group, user etc) have have three different states:

    *  Unassigned.
    *  Granted.
    *  Denied.

    If you are the member of two groups, and one group has not been granted nor denied permission on an object, and you the other group has been granted permission on the object, you have permission.

    But if one group has been denied access, it does not help if the other group has been granted access, because DENY takes precedence over GRANT. This appears to be the case in your case.

    DENY should be used with caution. Since by default users does not have any permission at all, there is little reason to use DENY just for the sake of it. Only if you want to add an exception to permissions that have already been granted DENY makes sense.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Warwick Rudd Sunday, March 11, 2012 7:12 PM
    • Marked as answer by Iric Wen Monday, March 19, 2012 8:47 AM
    Friday, March 9, 2012 10:48 PM

All replies

  • I think if the group 1 has exclusive deny permissions on that procedure, then the effective permission will be deny irrespective of whether you belong to group 2 which has execute permission on that procedure.

    But if group 1 has no permissions defined on that procedure, then you will have execute permissions if you belong to group 2(that has execute permissions on that procedure).


    Friday, March 9, 2012 9:07 PM
  • The permission for an action on an object granted to a principal (group, user etc) have have three different states:

    *  Unassigned.
    *  Granted.
    *  Denied.

    If you are the member of two groups, and one group has not been granted nor denied permission on an object, and you the other group has been granted permission on the object, you have permission.

    But if one group has been denied access, it does not help if the other group has been granted access, because DENY takes precedence over GRANT. This appears to be the case in your case.

    DENY should be used with caution. Since by default users does not have any permission at all, there is little reason to use DENY just for the sake of it. Only if you want to add an exception to permissions that have already been granted DENY makes sense.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Warwick Rudd Sunday, March 11, 2012 7:12 PM
    • Marked as answer by Iric Wen Monday, March 19, 2012 8:47 AM
    Friday, March 9, 2012 10:48 PM
  • Thank you Erland.

    gene

    Sunday, March 11, 2012 4:31 PM