How does SQL Server handle logins when the user is a part of multiple groups with various privileges?

Question

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?

• Aaron Bertrand: Simple way I think about it: (sysadmin or readonly) = sysadmin - basically, if they get rights one way then they have those rights even if they don't get them another way. The exception is DENY which overrides (except in the case of sysadmin IIRC). This is easy to set up and test (I'm just too lazy atm).

Answer 1

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.