Access 2007 Application for Reports Only – SQL Server Security

[Brad]

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?



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,
Brad

~~~~~~~~~~~~~~~~~~

 

[Tom van Stiphout]

On Thu, 20 May 2010 10:31:01 -0700, Brad

You can of course test this out on a backup of your database, but yes
I believe your steps are correct.

If you're logging in as a sqlserver user, it does not matter that you
also have windows auth rights.

-Tom.
Microsoft Access MVP

 

[Brad]

Tom,

Thanks for the help, I really appreciate it.

It looks like SQL Server security can become real complicated, especially
for people who are new to it. We sure don't want to make a mistake and leave
open a door in which someone with Access 2007 could change production data on
the database.

Thanks again,

Brad

 

[Stewart Berman]

What prevents the user from creating a new database and creating a new ODBC
connection that doesn't use the restricted userid and password? Since they
are not logged in as an sqlserveruser won't the system connect them using
their integrated authority?