What is effective permission if windows user is added as login as well as via group membership; with only public permission given via the user login?

Question

Link: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver15#principals

Remember that a Windows user might be a member of more than one Windows group (e.g. both Engineers, and Managers). Permissions granted or denied to the Engineers login, to the Managers login, granted or denied to the user individually, and granted or denied to roles that the user is a member of, will all be aggregated and evaluated to for the effective permissions.

If windows group is added as login and given db_owner permission on couple of dbs.

And then one of the members of this group (say domainname\UserA) is added as a login with only public role (at server and db level).

I know that denials override the grants. But does giving public permission inherently result in any denials?

In other words, what is the effective permission for this user (domainname\UserA)? Is it only public or does it retain the db_owner permissions?

Answer 1

Permissions can only be denied by an explicit DENY statement.

From the Docs

DENY denies a permission to a principal. Prevents that principal from inheriting the permission through its group or role memberships.

As a result, granting permissions via membership in any group does not take away any permissions. Grants are always additive, that is they are in-addition to the already existing permissions the security principal has.

So, to answer your question, membership in the public role does not prevent permissions assigned via group membership from having effect. In the scenario mentioned in your question, the user would have db_owner permissions via their membership in the group that has been granted membership in that role.