?
Solved

How can I Set Read Only Access to SQL Server tables in Access 2003?

Posted on 2014-11-20
9
Medium Priority
?
849 Views
Last Modified: 2014-11-21
I've written the following code to create a table in Access 2003 that's linked to a table in a SQL Server 2008 database, but the table gets write permissions even though the user "ReadOnlyUser" has only read permissions in SQL Server.  How can I create the linked tables with read only permissions?

    Dim DB As Database
    Dim tDef As TableDef
   
    Set DB = CurrentDb
   
    Set tDef = DB.CreateTableDef(strTableName)
    tDef.Connect = "ODBC;Driver={SQL Server};Server=" & strActiveServerName & ";DATABASE=DBNAME;Uid=ReadOnlyUser;Pwd=readonlyuserpwd;"
    tDef.Attributes = dbAttachSavePWD
    tDef.SourceTableName = "dbo." & strTableName
    DB.TableDefs.Append tDef
0
Comment
Question by:Declan_Basile
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40456007
If you are relying on SQL Server security, and the security isn't working,then you have a problem with th settings in SQL server.

I would suggest using SQL Server Profiler and have a look at the information generated when the connection is made, to make sure that it is the expected user is connected.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40456010
Access doesn't have anything to do with permissions on the server.  If the userID you are using in the connection string does not have update permissions, then Access can't override that.  Access sends updates to the server and the server decides whether or not to apply them.

Verify the permissions for the user.

Delete and then relink the tables.
0
 
LVL 1

Author Comment

by:Declan_Basile
ID: 40456046
The problem is that the tables are getting linked with credentials different than the ones I specify in the connection string.  I specify "Uid=ReadOnlyUser;Pwd=readonlyuserpwd;" in the connection string, but when I hover the mouse over the linked table after it's created, the connection string shows a different Uid.  It shows the UId of a SQL Server User that has write permissions.  Why is Access ignoring the credentials that I specify in the connection string?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 40456077
Have you actually relinked the tables after you changed the connection? You must do so - and in fact, I've always taken the time delete and recreate the links when I do that.
0
 
LVL 1

Author Comment

by:Declan_Basile
ID: 40456216
I physically deleted the linked table (Clicked on it and pressed "delete" key) and then ran the code I posted.  I believe it would give me an error that the table already exists if I didn't delete it first.  Why would Access ignore the credentials in the connection string and decide on it's own to use another user's SQL Server credentials?
0
 
LVL 1

Author Comment

by:Declan_Basile
ID: 40456248
I just tried deleting *all* linked tables, compacting the database, closing and opening it.  Then when I relinked the table it used the credentials specified in the connection string.  Apparently if there are already other linked tables Access uses their credentials instead of the credentials specified in the connection string to link new tables.  Do all linked tables in the same .mdb have to use the same credentials?
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 664 total points
ID: 40456268
I'm going to guess yes.  But it is a guess and you should verify.  I suggested deleting the links (I should have been more specific and said ALL the links) because I have had trouble in the past when trying to change credentials on the fly.  The only place I've actually attempted to link different tables with different credentials was to a Timberline database using Pervasive and that didn't work.  I wasn't doing it intentionally.  I was adding a new table to an existing database and didn't realize that a different developer had linked them using his own credentials.

You need to control the permissions on the server at the table level and then link all the tables using the same credentials.  I never thought about it but what would happen if you sent a query that joined two tables that were linked with different credentials.  So, the ODBC driver may simply not allow it.
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 668 total points
ID: 40456765
> Do all linked tables in the same .mdb have to use the same credentials?

No, but linked tables from the same database will have to.

/gustav
0
 
LVL 1

Author Closing Comment

by:Declan_Basile
ID: 40457176
Thank you everyone for your help!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question