3

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?

  • 3
    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). – Aaron Bertrand Oct 17 '14 at 15:25
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.