HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
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.