Results 1 to 5 of 5
  1. #1
    kjg48359 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Posts
    2

    SQL Security w/ Access Front End

    Hi, I have not been able to find this anywhere on the net, but we have a SQL security question.



    Hopefully I'll explain this simply enough.

    (environment: SQL Server 2010, Windows 7, Access 2007)

    Using 'Nameless' DSN connections to an SQL server (that is at 'connection' time we pass along the credentials to connect to an SQL server table by table, with server, db, user ID and pswd credentials for each connnection), we have defined an id using "Select" only rights (or read-only). (We're using SQL Server Authentication, not Windows Authentication).

    Through any other interface it does appear that updates are not allowed. However, from Access, if the user has a copy of Access, then they can create an action query, and they appear to work. Is there some kind of setting that we're missing on the SQL database, or within Access that enforces the access rights of that id in question? This is not behavior I would expect.

    thank you.

  2. #2
    kjg48359 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Posts
    2
    (as a quick followup, we have made sure that the db roles assigned to the ID that is used is db_datareader and db_denywriter)

  3. #3
    vincent-leeway is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    36
    Its not clear actually, do you mean you are using access front end to connect to MSSQL Server?

  4. #4
    MHansler's Avatar
    MHansler is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    1

    Access Front End database for SQL table Security

    I'm using SQL security for Access to link to a table on an SQL Server. The link works fine however, is there a way I can pass the SQL credentials from the access database without having to type them in to refresh the data every day?

  5. #5
    vincent-leeway is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    36
    Quote Originally Posted by MHansler View Post
    I'm using SQL security for Access to link to a table on an SQL Server. The link works fine however, is there a way I can pass the SQL credentials from the access database without having to type them in to refresh the data every day?
    I think it is very much possible by writing a small software program in .Net, which you can either execute it yourself every day and by one click you can achieve whatever your desire or you can also schedule it to execute at a particular time of the day.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Unable to Access DB Via Front End
    By mcktigger in forum Security
    Replies: 1
    Last Post: 12-16-2012, 04:31 PM
  2. Using Access as Front End for SQL Server
    By workinstiff in forum Access
    Replies: 4
    Last Post: 07-18-2012, 12:16 PM
  3. Access front end hangs when more than one user
    By mafrank101 in forum Access
    Replies: 3
    Last Post: 05-31-2012, 10:15 PM
  4. Link SQL Database to new Access front end
    By gumbi17 in forum SQL Server
    Replies: 3
    Last Post: 02-07-2012, 10:07 PM
  5. Replies: 1
    Last Post: 10-02-2011, 05:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums