Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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
share|improve this answer
    
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. –  Sean Howat Jun 9 '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 9 '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: social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/… 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

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.

share|improve this answer
    
How is this possible? –  hyty Jun 12 '10 at 5: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. –  califguy4christ Jun 14 '10 at 19:41

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.

share|improve this answer
    
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 5:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.