I'm trying to wrap my head around how SQL Server decides to grant certain rights based on a users login privileges. If a user is part of a group "READ ONLY" which has access to DB1, but they are also a part of a group "SysAdmin" which has full db owner rights to the same DB1, which rights are they given when they log in? On a similar note. What if they are part of a group but also have a standalone account with other middle of the road privileges?
SQL Server – Handling Logins with Multiple Group Privileges
Securitysql server
Best Answer
For normal roles, permissions for all role memberships are cumulative and DENY takes precedence over GRANT. However, sysadmin is a special case. SQL Server permissions are not checked for sysadmin role members so members of that role have full permissions to all databases on the instance.