Sql-server – Managing SQL Server users via Active directory groups

active-directorysqlsql serversql-server-2008windows-server-2008

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?

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
Related Topic