[AccessD] Access 2007 Application for Reporting Only – SQL Server Security

 About a week ago, I posted a preliminary question on this topic.  With the
advice that I received, we have made nice progress.  I now have a couple
follow-up questions to confirm that we are on the right track and not
missing something important.



Background  - Small firm  / No DBA / Purchased package that stores data in
SQL Server 2005



We are developing a new reporting system with Access 2007.  We want to
GUARANTEE that this new system will NEVER be able to update any SQL Server
tables.



No one has much SQL Server security experience, so we would like to know if
we are on the right track.







Here is what we are planning to do -



Set up new SQL Server Login (at the Server level)



Set up new SQL Server User (at the Database level) tied to the SQL Server
Login we just set up



Assign this new SQL Server User the role of db_datareader (and nothing else)



Change Server Authentication from “Windows Authentication Mode” to “SQL
Server and Windows Authentication Mode” (Done at the Server Level)



Set up a new ODBC connection with our new Login and User



Tie the Access 2007 Reporting Application to this new ODBC connection



Are these the proper steps?  Are we missing anything?







Also we have a question about one authentication overriding another…



Currently all users have Windows Authentication that permits them to update
the SQL tables in the purchased system.  If our new Access reporting system
uses our new ODBC connection with the User that only has db_datareader will
this prevent any table updates via our Access system, or will the original
Windows Authentication that allows updates override this and allow updates?





Thanks in advance for your assistance.

Brad