2

I'm building SQL Server instance for reporting purposes. My plan is to use AD groups for server and database logins. I have several groups with different roles (admin, developer, user etc.), and I would like to map these roles into SQL Server database roles (db_owner, db_datawriter etc.). What are the pros and cons of using AD groups for logins? What kind of problems you have noticed?

hyty
  • 431
  • 1
  • 5
  • 6

3 Answers3

3

Other than the overhead of having to manage AD in the first place, I don't think there are any cons. Using windows login credentials for SQL Server, particularly in the fashion your talking about with organized role groups, is certainly a best practice recommendation from Microsoft. If they had their way, they'd take the option for SQL Server authentication out completely.

Addendum:

If you are using SQL 2005 or above, use Default Schema option (don't think there is a GUI option for this), by:

ALTER USER userName  
     WITH <set_item> [ ,...n ]

<set_item> ::= 
     NAME = newUserName 
     | DEFAULT_SCHEMA = schemaName
     | LOGIN = loginName

ie:

ALTER USER DOMAIN\UserName DEFAULT_SCHEMA = dbo;
GO
Andrew Bickerton
  • 105
  • 6
Ryan Bolger
  • 16,755
  • 4
  • 42
  • 64
  • Yeah from where I'm sitting I only see advantages. We're running SQL Server in a non-AD environment and I know there's been times when I could have done thing way easier by having it available. – Shane Jun 09 '10 at 15:51
  • I tested using groups and I noticed that when a user assigned to a group which is e.g. db_ddladmin, and this user creates a table, a schema based on a name of this user is created. Very odd. So the table becomes domain\user.testtable (schema.table). And there is no possibility to assing dbo schema as a default to AD groups. This is definitely a con. How to solve this problem? – hyty Jun 09 '10 at 16:37
  • If you haven't had the displeasure of dealing with the fact that you can't assign a default schema to an AD group go read about the headaches: http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/7d46a024-7ed5-4c9b-b091-3640dc04f5a1/ Supposed SQL Server codename Denali is *FINALLY* going to allow this functionality. In the meantime you end up having to create gobs of individual users in the database to assign default schemas. – Evan Anderson Jul 28 '11 at 14:34
1

The management of Active Directory groups can also be delegated to non-Active Directory administrators, which can be a handy feature short of an in application management tool.

  • How is this possible? – hyty Jun 12 '10 at 05:33
  • Right click the security group in Active Directory and add a user to the Name field on the Managed By tab. Then, I believe, the user would need the Server Administrator Tools pack to get ADUC access from their workstation. –  Jun 14 '10 at 19:41
0

Biggest con I've run into is 3rd party apps that don't support AD authentication and insist on using SQL authentication, usually with creative logins like admin/admin; barring that, the only other issue is you don't have total visibility on the SQL server of who has access to the databases, you only see the group, or see the users when they are active, or if they own objects. But as long as your DBA has access to Active Directory if he needs user-level info, it's a very minor issue.

SqlACID
  • 2,176
  • 18
  • 18
  • Thanks, yes, we have access to AD and also your reporting system (SAP Business Objects) has built-in AD authentication. – hyty Jun 12 '10 at 05:35