Solved

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

Posted on 2014-11-20
9
759 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 37

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 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 37

Accepted Solution

by:
PatHartman earned 166 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 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

724 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