Home / server / Questions / 149680
Accepted
hyty
Asked: 2010-06-10 07:30:46 +0800 CST

Managing SQL Server users via Active directory groups

  • 772

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?

windows-server-2008 sql-server sql-server-2008 sql active-directory

3 Answers

  1. Best Answer

    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
    • 3

  2. 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.

    • 1

  3. 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.

    • 0