Solved

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

Posted on 2014-11-20
9
659 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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