Should I create NT Authenticated Logins mapped to Active Directory groups?

Question

We want to use Active Directory groups to restrict access to SQL Server 2005 databases. I see that you can create an Login in SQL (using Windows authentication) mapped to an AD group, but is this a good idea? The obvious benefit is that we would be able to maintain group membership through AD, which we already do for network access and various applications. Are there any other benefits? What about drawbacks? I imagine it could get a bit hairy if an AD user belongs to multiple AD groups that exist as separate Logins.

My current plan to create Schemas to contain the various groups of securable database objects. Then I'd create users with the appropriate default Schema and only give access to other Schemas if they need it.

Answer 1

You can't map a "SQL login" to an AD group: you grant access to an AD group at the SQL level. I say this because "SQL Login" implies username and password.

In any event, it's easier to manage AD groups. And what would your alternatives be in practice?

If a user is in two AD groups, then you need controls to check this. Or use a Logon trigger to test for multiple memberships and reject access

Answer 2

SQL Server supports two kinds of logins: SQL authenticated, and NT authenticated. You are describing NT authentication. For years, both Microsoft, industry experts, and just about every book I've read strongly recommend using only NT authentication, and not using SQL authentication at all. In fact, you can disable SQL authentication, but you cannot disable NT authentication.

In other words, yes, this is the way to go.