Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link to SQL7 (Access97)

    Hi there, I have an Access db linked to a SQL7 db for reporting/mailmerge etc.
    How can i ensure that the Access Front End does not allow updates to the database data please ?
    I want read only access but cannot find anywhere in the ODBC set up that defines this !?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Link to SQL7 (Access97)

    The simplest way is to set permissions in SQL so users don't have update permissions. Unfortunately it's easier said than done. Most installations run with NT security, so each user has a SQL account that matches their NT login name, and if they have admin priviledges, they may also have the same priviledges as SA, the system account. You can also set the priviledges in the Access FrontEnd database, but you will want to activate Access security to do that. As far as I know, there isn't any easy way to set an ODBC connection to be read-only.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL7 (Access97)

    I thought about the SQL/NT security path but discounted it after realising that the one user of this database is also a user of the main (multi user) database (browser based application with it's own security/permissions based on NT login). I think the best solution, if ODBC or the table properties within Access cannot be set to read only is to create a specific NT account and then create the ODBC connection as that user. Would you agree ?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Link to SQL7 (Access97)

    SQL Server permissions don't have to be set for NT security. That can be changed, even after the fact.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to SQL7 (Access97)

    I want the extra configurability of NT permissions (groups/users etc) for the main database. For the single user database (via Access) i want to give this one user read only permissions. They are using Access to build reports, mail merge, etc output and i dont want them to update the data in any way through this interface for a number of reasons
    1 - they could update/remove the contents of a whole table without any validation, via a query or table view
    2 - any changes they make - if they should be valid updates - will not be recorded in the database audit log

    Can anyone see any problems if i create a new NT account - eg : ReportUser and give that user read only permissions to the database via sql server, then create the ODBC link to use that account ?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Link to SQL7 (Access97)

    There shouldn't be any problem in creating it and setting SQL permissions for it as long as you make sure the ODBC connection is using that login ID, but it will mean the user will need to log off and log back on as the restricted user. Also, how do you restrict them from getting at the Access front-end via their normal login?

    I think I would create a special SQL login and not let them use the integrated security for the ODBC connection - just make sure they don't belong to the NT admin group.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •