If a Windows AD Group and an AD Account that belongs to that AD Group are both setup as Logins on a SQL server, will the AD Account Login inherit all the User Mappings that it's parent AD Group Login are currently mapped to?

Or would I need to re-apply the same User Mappings to the AD Account Login that are already setup on the parent AD Group Login?

  • Why would you add both? Wouldn't you just add the AD group and assign the privileges to the group then the users within would get those permissions, and would lose permissions if/ when removed from the group – Stockburn Sep 11 '19 at 0:30
  • @Stockburn Because I want to provide additional customized privileges to the individual user on top of the privileges he gets from the group. So I was wondering if I have to duplicate the mappings his group already has onto his Login or if they'll be inherited automatically and I only need to adjust the additional permissions I want to provide. (E.g. the AD account "Domain Users" all have read access to most of our databases, but this particular user should have write access to a specific database.) – J.D. Sep 11 '19 at 12:36

Yes, a Windows account will have all of the permissions that are assigned directly to the login as well as any permissions that are assigned to any groups the account is a member of.

Permissions are cumulative, but DENYs will override. It would be difficult or impossible to accommodate some security needs if they were not.

See Effective SQL Server permissions when user is in several AD groups. The same principals apply to permissions assigned to a user and to a group they are a member of.

In general, DBAs that have been around a while will advise you to never start giving permissions to individual user accounts, as you can then end up with a long list of logins, and when employees leave you have to go clean them up. The preferred way is to only assign permissions to groups, and then when an employee leaves, or is hired, the DBA doesn't have to do anything as the request for access is granted by whoever manages the AD groups.

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.