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 "role" for each application 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?
2 Answers
Option 1 is your best bet.
Granting access to Public is not a good idea. It could be the case that a user would change job functions and at that point you would not want the Public role to have access to your data. Unless it's the case that literally EVERY person in your organization gets access to your app.
Creating a group and then assigning it to the datareader role is not necessarily a good idea because there might be data you don't want everyone in the role to read (thus your "specific database objects" specification).
If you create a group and then give it SELECT/EXECUTE/etc access to the objects in question that should do it for you.
- 37,618
- 10
- 90
- 145
-
Just to add to the discussion... for most of the applications/data that needs to be secure, I wouldn't give the domain\domain users group the "viewer" role. That group only will get the "viewer" role if the data doesn't need to be secured. I definitely don't want to grant rights to the public role in SQL as you said. I also have avoided the datareader and datawriter roles as well, opting for the custom database roles instead. I tried using the domain\domain users group and gave them my custom "viewer" role and it seems to be working ok so far. – Mike Thien Mar 18 '10 at 18:43
You could always grant access to various securable objects to the Public server role. Unless specifically granted or denied privileges , every user inherits permissions granted to Public.
- 730
- 2
- 12
- 28