link table

  • Thread starter Thread starter LL
  • Start date Start date
L

LL

Hi, I create a user(user1) only have "read" permission in SQL server. and
through ODBC(use user1) and Access to create a link table to access the sql
server database.
At my clinet's computer, I at least saw two situations:
1. one of the computer, Access still can modify the link table but won't
save to sql
2. can modify the link table and the changes saved to sql.

I think the normal way shouldn't have permission to WRITE(UPDATE) table.
what's happening? how to track it down?

Actually I found when I create the new DB, and build the link talbe, it's
ready only.
Only after I copy it to another user's profile folder(C:\Documents and
Settings\if.CDE\desktop)
then the the link table change to can writable?

and in the sql trace tool, it shows "cde\if" as the NTUserName to execute
the query not the connected user(readonly)?

Any ideas? thanks.
 
If SQL Server is set for SELECT permission only on the table then there is
NO WAY that Access can make it writable.

Therefore, the user who can make the table writable has OTHER PERMISSIONS in
SQL Server that allow this.
Perhaps the SQL Server uses Windows accounts too.
And your user who can edit data has Guest account privileges with Insert,
Update, Delete on that table.
 
Thanks Joe.

I didn't set it for SELECT permission only, but I use db_datareader Database
role instead.
Is it the same?

Yes, my SQL Server uses SQL and Windows authentication. But on the ODBC
configure window"Microsoft SQL Server DSN configuration", I specify using
SQL Server authentication
and specify the LoginID and Password.
In this case, should use SQL's login ID, am I right?

I found two situations I don't understand:
1. On my laptop(windows XP and access XP), when I configure the ODBC as
"File DSN", the first time when I create the link table, it will pup up a
SQL Server Login Window, something like:
SQL Server Login
Data Sorce: SQLDSN
(checkbox) Use Trusted Connection
Login ID:
Password:
OK Cancel
after that, if I select a sql tabke as link table and try to modify it, will
get an error: CDBC-- update on a link table 'dbo-Referal' failed.
[Microsoft][ODBC SQL Server
Driver][SQL Server]UPDATE permision denied on object "Referral", database
"Test", owner'dbo'.(#229)
....
But If I save it and reopen the access db, then the sql login pupup window
won't show.
and I can modify the link table directly.
when I point the mouse to the link table, it shows "...Trusted
Connection..." in a tooltip. Because my login account(to window) is an
administrator account, so I guess i can make the changes to SQL server.

If I define the ODBC source as a System DSN, then every time the SQL login
window will pup up.
Why is that???

2. On my client machine, I login to the system through Termial Server
Service, the SQL login widow won't pupup in both case(defined ODBC as "File
DSN" or "System DSN", and always using "Trusted Connection". Why is that
again???

Pls advice.
Thanks...
 
FWIW,
I always use Systems DSNs and no Trusted Connections.

I also run re-connect code that uses a single application account with
specific rights.
e.g. SELECT permission only on all tables.

Code sample:
I use this procedure to re-create links to SQL Server.
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub

--
Joe Fallon
Access MVP



ll said:
Thanks Joe.

I didn't set it for SELECT permission only, but I use db_datareader Database
role instead.
Is it the same?

Yes, my SQL Server uses SQL and Windows authentication. But on the ODBC
configure window"Microsoft SQL Server DSN configuration", I specify using
SQL Server authentication
and specify the LoginID and Password.
In this case, should use SQL's login ID, am I right?

I found two situations I don't understand:
1. On my laptop(windows XP and access XP), when I configure the ODBC as
"File DSN", the first time when I create the link table, it will pup up a
SQL Server Login Window, something like:
SQL Server Login
Data Sorce: SQLDSN
(checkbox) Use Trusted Connection
Login ID:
Password:
OK Cancel
after that, if I select a sql tabke as link table and try to modify it, will
get an error: CDBC-- update on a link table 'dbo-Referal' failed.
[Microsoft][ODBC SQL Server
Driver][SQL Server]UPDATE permision denied on object "Referral", database
"Test", owner'dbo'.(#229)
...
But If I save it and reopen the access db, then the sql login pupup window
won't show.
and I can modify the link table directly.
when I point the mouse to the link table, it shows "...Trusted
Connection..." in a tooltip. Because my login account(to window) is an
administrator account, so I guess i can make the changes to SQL server.

If I define the ODBC source as a System DSN, then every time the SQL login
window will pup up.
Why is that???

2. On my client machine, I login to the system through Termial Server
Service, the SQL login widow won't pupup in both case(defined ODBC as "File
DSN" or "System DSN", and always using "Trusted Connection". Why is that
again???

Pls advice.
Thanks...



Joe Fallon said:
If SQL Server is set for SELECT permission only on the table then there is
NO WAY that Access can make it writable.

Therefore, the user who can make the table writable has OTHER
Click to expand...
PERMISSIONS
in
SQL Server that allow this.
Perhaps the SQL Server uses Windows accounts too.
And your user who can edit data has Guest account privileges with Insert,
Update, Delete on that table.
--
Joe Fallon
Access MVP



the
sql
Click to expand...
Click to expand...
 
Thanks Joe.
I always use Systems DSNs and no Trusted Connections.

I also run re-connect code that uses a single application account with
specific rights.
e.g. SELECT permission only on all tables.
Click to expand...

How to force system will pup up the SQL login window every time when I
reopen the link table? or re-configure the link table is not a Trusted
Connection.

If you give me a read only privileges account of your SQL server, and I use
to build a link table through Access and reopen the link table and make some
changes to SQL db. whose fault? I think the ODBC wizard will build the ODBC
source as a File DSN as default.





Joe Fallon said:
FWIW,
I always use Systems DSNs and no Trusted Connections.


Code sample:
I use this procedure to re-create links to SQL Server.
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub

--
Joe Fallon
Access MVP



ll said:
Thanks Joe.

I didn't set it for SELECT permission only, but I use db_datareader Database
role instead.
Is it the same?

Yes, my SQL Server uses SQL and Windows authentication. But on the ODBC
configure window"Microsoft SQL Server DSN configuration", I specify using
SQL Server authentication
and specify the LoginID and Password.
In this case, should use SQL's login ID, am I right?

I found two situations I don't understand:
1. On my laptop(windows XP and access XP), when I configure the ODBC as
"File DSN", the first time when I create the link table, it will pup up a
SQL Server Login Window, something like:
SQL Server Login
Data Sorce: SQLDSN
(checkbox) Use Trusted Connection
Login ID:
Password:
OK Cancel
after that, if I select a sql tabke as link table and try to modify it, will
get an error: CDBC-- update on a link table 'dbo-Referal' failed.
[Microsoft][ODBC SQL Server
Driver][SQL Server]UPDATE permision denied on object "Referral", database
"Test", owner'dbo'.(#229)
...
But If I save it and reopen the access db, then the sql login pupup window
won't show.
and I can modify the link table directly.
when I point the mouse to the link table, it shows "...Trusted
Connection..." in a tooltip. Because my login account(to window) is an
administrator account, so I guess i can make the changes to SQL server.

If I define the ODBC source as a System DSN, then every time the SQL login
window will pup up.
Why is that???

2. On my client machine, I login to the system through Termial Server
Service, the SQL login widow won't pupup in both case(defined ODBC as "File
DSN" or "System DSN", and always using "Trusted Connection". Why is that
again???

Pls advice.
Thanks...



Joe Fallon said:
If SQL Server is set for SELECT permission only on the table then
Click to expand...
Click to expand...
there
is PERMISSIONS server.
and
Click to expand...
Click to expand...
 
Back
Top