2

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.

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

  • Cool. I have not encountered any issues as of now. Since I have one single user name mapped to the Windows Group Login, I was wondering if that is OK or if I need individual User names set up for each user in the Windows Group. Thanks!. – thinkster Oct 12 '16 at 16:01
  • No need for individual SQL logins - that is the beauty of mapping to a domain group. Control access at the domain administration level. – Gary Oct 12 '16 at 16:03
  • Gary beat me to it while I was typing but I'll finish anyway. Depending on how you set it all up, once you create the SQL Server login (server level) you have the option at the same time to allow that login to connect to databases and grant specific roles in databases (essentially granting database level access). This is generally all you need to do. No extra steps required unless you want a specific member of the group to have different permissions from the group. – Antoine Hernandez Oct 12 '16 at 16:05
  • @Gary - Got it. I was trying to get away with the step of adding Users and then mapping it to the Wndows Group if the Windows Group Login could be mapped to Roles directly. I believe it is fine to the current set where I have single user name mapped to the entire Windows Group. – thinkster Oct 12 '16 at 16:13
  • @Antoine Hernandez - Is it possible to have the Login mapped to Roles without a User Name mapping for Windows Authentication. Thanks. – thinkster Oct 12 '16 at 16:13
0

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.

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.