SQL Server - Windows Group Authentication 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  -&gt; domain\AdminGroup   
Database Name       -&gt; XYZ    
DB Login Name       -&gt; domain\AdminGroup   
DB Role             -&gt; xyzAdmin   
User Name           -&gt; 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.
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