SQL Server - Windows Group Authentication

Question

I am trying to understand how to create Windows Group based access to a SQL Server (2012) database the right way. I was able to successfully add a Windows group to the Server Login and I have a defined Role to which I would like to have the group mapped. My question is, does SQL Server need a User created and mapped to the Windows Group to allow users with Windows Authentication access the database? If so should it be per user or can I just have a single User Name for the Windows Group Login.

Just to demonstrate how I have the current set up:

Windows Group Name  -> domain\AdminGroup   
Database Name       -> XYZ    
DB Login Name       -> domain\AdminGroup   
DB Role             -> xyzAdmin   
User Name           -> xyzAdminUser (mapped to Role: xyzAdmin and Login: domain\AdminGroup)

Currently I have created a generic User (not individual ones for each user in the group) and mapped it to the Windows Group Login. But I am not sure why that step is required since it is Windows Authentication. Is that something more of a requirement to have explicit mapping from Login to User Name which SQL Serve requires? Not sure if SQL Server could handle this implicitly behind the scenes if there is any option to map a Windows Group Login to a Database Role. The reason I am asking is, if a User name needs to be created explicitly for either Windows Group as a whole or for individual users, I am not really sure if I as a User would ever need to use it since it is Windows based authentication unlike other login where I use my User Name and password to authenticate.

Answer 1

You were correct in creating the Windows domain security group. Add Windows domain accounts to that security group as required. You were correct in creating a SQL login for the Windows domain group. SQL will allow authentication by any member of that Windows domain group. Nothing further is required as far as SQL login creation. Set permissions as needed within SQL Server.

Have you tested your configuration? What problems are you encountering?

By using Windows Authentication, Windows groups can be created at the domain level, and a login can be created on SQL Server for the entire group. Managing access from at the domain level can simplify account administration.

Books Online

Answer 2

I will try to collect the information and try to answer specific questions I had.

What I understand is, you could map a Login (Server-Level Principal) to a Database Role (Database-Level Principal) through the User Mapping option or script. What SQL Server does when you try mapping a Login to a Database is, it shows you a list of Memberships (Roles) available to choose from that Database. If you select one, then the corresponding Role gets mapped to a new User which SQL Server creates implicitly for you. User column on the User Mapping Section of Login Properties will be populated with the Login Name (Windows User or Windows Group Name) by default which can be modified if required. Once confirmed, SQL Server automatically creates the new User object on the Database.

In short, for a Windows based SQL Server Login, there is no need for an explicit creation of User object as this will be taken care by SQL Server automatically during the process of selecting required Membership when mapping the database for your Login.

Although few years old, these posts will provide a good insight to security basics.