SQL Server Windows-only Authentication Strategy problem
- by Mike Thien
I would like to use Windows-only Authentication in SQL Server for our web applications. In the past we've always created the all powerful 1 SQL Login for the web application. After doing some initial testing we've decided to create Windows Active Directory groups that mimic the security roles of the application (i.e. Administrators, Managers, Users/Operators, etc...) We've created mapped logins in SQL Server to these groups and given them access to the database for the application. In addition, we've created SQL Server database roles and assigned each group the appropriate role. This is working great. My issue revolves around that for most of the applications, everyone in the company should have read access to the reports (and hence the data). As far as I can tell, I have 2 options: 1) Create a read-only/viewer AD group and put everyone in it. 2) Use the "domain\domain users" group(s) and assign them the correct roles in SQL. What is the best and/or easiest way to allow everyone read access to specific database objects using a Windows-only Authentication method?
