Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

SQL Server and Active Directory Groups

SQL Server Active Directory Group

Windows Authentication to SQL Server for multiple people needing the same level of authorization is generally accomplished one of two ways:

  • Option 1: create a group in Active Directory, add Windows logins to the AD group as needed, and create a SQL Server login for the AD group. From there, grant permissions to the SQL login (and its related database users) as needed.
  • Option 2: create individual Windows Authentication logins to SQL Server for each person, then grant the same permissions to each SQL login (and their related database users).


    • Authorization

    With either option, you can mandate what level of authorization is allowed. You remain in control of things such as:

  • Which server-level roles a login is a member of.
  • Which database user(s) are associated with each login.
  • Which database roles a database user is a member of.
  • Individual GRANTs/DENYs on database users.


    • Authentication

    Here's where there is a trade-off. If you work in an environment where someone else wears the hat for AD management, you don't control AD group membership. That means with Option 1, you lose control over who can authenticate (login) to SQL. This can be particularly worrisome when elevated permissions are involved. Option 1 *does* have the benefit of convenience. Anyone that needs "access" can be added to the AD Group. When someone no longer needs access (change in job duties, termination of employment, etc.), simply remove him/her from the AD Group.

    Option 2 is...not convenient. You have to create and maintain multiple logins, and on multiple SQL instances. There's more work to be done, but you have complete control over authentication.

    Both options are workable, so let's avoid the temptation of saying either option is right or wrong. Rather, make an informed decision and know the pros and cons of each approach.

    Comments

    Post comment