patternsqlMinor
How does SQL Server handle logins when the user is a part of multiple groups with various privileges?
Viewed 0 times
thegroupshandlesqluserpartloginswithvariousprivileges
Problem
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?
Solution
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.
Context
StackExchange Database Administrators Q#80492, answer score: 6
Revisions (0)
No revisions yet.