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
Solved

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

Posted on 2014-11-20
9
694 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 35

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 84

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 35

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 49

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

839 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