Access 2007 Application for Reports Only – SQL Server Security

Discussion in 'Access Security' started by Brad, May 20, 2010.

  1. Brad

    Brad Guest

    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

    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?


    Brad, May 20, 2010
    1. Advertisements

  2. 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.

    Microsoft Access MVP
    Tom van Stiphout, May 21, 2010
    1. Advertisements

  3. Brad

    Brad Guest


    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, May 21, 2010
  4. 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?
    Stewart Berman, May 30, 2010
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
There are no similar threads yet.