locked
SQL Server logins - use individual users or groups? RRS feed

  • Question

  • I was reading in the MS Self Training Kit 70-432 for SQL Server 2008 which recommends using Windows groups for managing security access:

    "Using Windows groups provides the greatest flexibility for managing security access. You simply add or remove accounts from the group to control access to a SQL Server instance." (p. 264 Ch11.3)

    Now I'm reading the whitepaper "SQL Server 2012 Security Best Practices" and on page 29 I'm reading this:

    "Use Windows Logins rather than Windows Group to control access to SQL Server and use care when using Windows Group logins to prevent group overlap for a particular user."

    Now i realize the first book is for 2008 and the whitepaper is for 2012, but the principles should be similar. I'm inclined to use groups - what's your take on this? Any previous experience that would steer you in one direction over the other?

    Thanks,
    Paul


    • Edited by PolishPaul Thursday, May 2, 2013 3:07 AM reformatted font styling
    Wednesday, May 1, 2013 9:28 PM

Answers

  • Security things can always be viewed from different angles.

    I am more incliened to agree wity the Self Training Kit than the white paper. Exactly as it says: you work with group membership in a single place the AD.

    I guess what is Bob's point is the risk that when you move people around in the AD that may be for different reasons than groups in SQL Server, and particularly you can get confusing results when a login is member of multiple groups. You intend that a person should be readonly in one database, but because he is a member of another groups he has stronger powers. Or no powers at all because he is member of a group that has been explicitly denied access.

    I've alerted Bob of this thread, and I hope that he will jump in and give his view on the matter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Monday, May 6, 2013 7:08 AM
    • Marked as answer by PolishPaul Monday, May 6, 2013 5:47 PM
    Wednesday, May 1, 2013 9:49 PM
  • Windows Groups usage can cause some ambiguities, for Windows users belonging to more than one group when both (all) groups have SQL Server access, both at the login level and user level. For login level, for example, default database and default language are set based on login. At user level, default schema is set per-user. Prior to SQL Server 2012, Windows group users cannot have a default schema.

    If a specific Windows user belongs to more than one Windows group with conflicting login information (e.g. FrenchUsers group with default language of French and EnglishUsers group with default language of US English), the default language (in this case) is set based on the first group to be returned by a Windows group enumeration API. Unexpected results may occur.

    If you're sure you don't have any such overlap (e.g. Windows users only belong to one Windows group with SQL Server access and conflicting defaults) then you can use Windows Groups sucessfully.

    Cheers, Bob

    • Proposed as answer by Fanny Liu Monday, May 6, 2013 7:08 AM
    • Marked as answer by PolishPaul Monday, May 6, 2013 5:47 PM
    Wednesday, May 1, 2013 10:00 PM

All replies

  • Security things can always be viewed from different angles.

    I am more incliened to agree wity the Self Training Kit than the white paper. Exactly as it says: you work with group membership in a single place the AD.

    I guess what is Bob's point is the risk that when you move people around in the AD that may be for different reasons than groups in SQL Server, and particularly you can get confusing results when a login is member of multiple groups. You intend that a person should be readonly in one database, but because he is a member of another groups he has stronger powers. Or no powers at all because he is member of a group that has been explicitly denied access.

    I've alerted Bob of this thread, and I hope that he will jump in and give his view on the matter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Monday, May 6, 2013 7:08 AM
    • Marked as answer by PolishPaul Monday, May 6, 2013 5:47 PM
    Wednesday, May 1, 2013 9:49 PM
  • Windows Groups usage can cause some ambiguities, for Windows users belonging to more than one group when both (all) groups have SQL Server access, both at the login level and user level. For login level, for example, default database and default language are set based on login. At user level, default schema is set per-user. Prior to SQL Server 2012, Windows group users cannot have a default schema.

    If a specific Windows user belongs to more than one Windows group with conflicting login information (e.g. FrenchUsers group with default language of French and EnglishUsers group with default language of US English), the default language (in this case) is set based on the first group to be returned by a Windows group enumeration API. Unexpected results may occur.

    If you're sure you don't have any such overlap (e.g. Windows users only belong to one Windows group with SQL Server access and conflicting defaults) then you can use Windows Groups sucessfully.

    Cheers, Bob

    • Proposed as answer by Fanny Liu Monday, May 6, 2013 7:08 AM
    • Marked as answer by PolishPaul Monday, May 6, 2013 5:47 PM
    Wednesday, May 1, 2013 10:00 PM
  • Hi,

    Even I have faced the issue with windows groups where the login (windows user) was existing in two windows groups both having access to SQL server and one windows group was having deny permissions for some objects which made the login not to access objects.


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    • Proposed as answer by Fanny Liu Monday, May 6, 2013 7:08 AM
    Thursday, May 2, 2013 12:44 AM
  • Hello,

    Just addtion to above. Beginning with SQL Server 2012, the default schema can also be set for user based on a group login.
    If the user has a default schema, that default schema will used.However,if the user does not have a default schema, and is a member of more than one group that has a default schema, the schema of the Windows group with the lowest principle_id will be used. (It is not possible to explicitly select one of the available default schemas as the preferred schema.) If no default schema can be determined for a user, the dbo schema will be used.
    Reference:http://msdn.microsoft.com/en-in/library/ms173463.aspx

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Fanny Liu Monday, May 6, 2013 7:08 AM
    Thursday, May 2, 2013 2:40 AM
  • Great! Thanks for the feedback!

    I was imagining a scenario where additional Windows groups would be set up based on SQL roles, then those would be used to manage SQL logins. For example, there would be HR, IT and Payroll user groups in AD and SQL_Admins, SQL_BackupOps and SQL_DataUsers groups. Although the users belong in the "departmental" roles, they'd also be part of a "SQL Role" AD group. I'm not sure if that is how things are typically done or best practices, but that would make sense to me when using groups.

    I was also wondering if there were any other "gotchas" with user vs group. I found one where I had given my group access to SQL Server level role as sysadmin. Remotely I could connect fine, but when using SSMS locally on the server, i had to run-as-admin to be able to connect. This was explained to me in:
    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/3cc12347-6668-448c-8ad4-400d52928788

    Thursday, May 2, 2013 3:06 AM
  • We use mutually exclusive groups so that security administration does not involve SQL DBA involvement. We have an AD group that has access to the database and so if we need to grant an additional user to said database our security team merely needs to add that user to the group. This also assists with security audits since each user is in a managed AD group and not merely mapped to the database so if a person changes roles the security team only needs to review their AD group membership.

    My perspective is from Healthcare IT where direct access to the databases is not typically granted and most database interaction is via service accounts. This approach is difficult to implement in complex security environments as previously mentioned.

    Thursday, May 9, 2013 5:32 AM