?
Solved

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

Posted on 2014-11-20
9
Medium Priority
?
1,173 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
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 41

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 41

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 53

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

601 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