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

If you don't want to embed the login information in the ODBC connection,
you could setup a separate database on you SQL server that only has
views looking at the production database.  Then grant only datareader
rights to the 2nd database and use it for your reporting application.

Rusty 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Friday, May 21, 2010 6:17 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
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************