orlando.ramon (7/1/2016)
If I have an AD user defined as an SQL LOGIN (AD\john.doe) with sysadmin privilege and is a member of an AD GROUP that is also an SQL SERVER LOGIN with only public privilege. What authority will this user (AD\john.doe) have when connecting to the server ADMIN or PUBLIC?
Login permissions are cumulative so if one group gets sysadmin and the other public than that login has sysadmin.
Also of note any deny permissions will overwrite grant permissions as that is the MS paradigm for applying permissions.
(Explicit Deny overrides Grant)
Group1 has Grant on Database1 and Deny on Database2
Group2 has Deny on Database1 and Grant on Database2
Login belongs to Group1 and Group2 will have deny on Database2 and Database1.
I've actually encountered this before with the explanation from the "expert" at the time as it should work because it makes logical sense....my response....whoever said Microsoft was logical?
EDIT***
I should have stated that the fixed sysadmin server role bypasses permission checks as sysadmin has permission to do anything on the server. Everything else about cumulative permissions is correct though.