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.
Best Answer
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?
Books Online