Bill Patten
bill_patten at embarqmail.com
Fri May 21 10:28:22 CDT 2010
Hi Brad, That sounds like it would do what you want, however there may be another route for the users. If the PC that has your program and the person logging in to it has permission to use the SQL Server with windows authentication and has full time Access he (or she) could create an ADP and log into the SQL server and do what ever they have permission to do.. If they do not currently have full time Access, then your solution should probably be built on run time Access which would not allow the user to create an ADP. Just a thought.. Bill -------------------------------------------------- From: "Brad Marks" <brad.marks1 at gmail.com> Sent: Friday, May 21, 2010 4:16 AM To: <accessd at databaseadvisors.com> Subject: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com