Permissions on tables linked to SQLServer

[Brian]

Hello,
I've just noted some odd behaviour and I'm wondering how to fix it.
In my SQLServer I have a public user called CUUser that only has select
permissions only.
I originally had created a file dsn to use to link the tables using the link
table menu option and I set the username and password as that of CUUser and
I also ticked 'Save Password'
If I try modifying data in the linked table I an blocked and correctly so.
I looked at the currentdb.tabledefs("mytable").connect property and got
ODBC;Description=SQL CUMME;DRIVER=SQL
Server;SERVER=SERVER\SQL2K;UID=CUUser;PWD=CUUserPassword;APP=CUMME;WSID=XPVM01;DATABASE=CreditData

I did some VBA code to relink the all the tables and I put
td.Connect = "ODBC;Description=SQL CUMME;DRIVER=SQL
Server;SERVER=SERVER\SQL2K;UID=CUUser;PWD=CUUserPassword;APP=CUMME;WSID=XPVM01;DATABASE=CreditData
td.RefreshLink

My application also then opens an ADO connection using CUAdmin user (which
does have update permissions) to do some stuff and it leaves the connection
open.

The odd thing is that I can now go back and actually modify data in the
linked table that I could not before!! When I look at the connect property
it is now
ODBC;Description=SQLCUMME;DRIVER=SQL
Server;SERVER=SERVER\SQL2K;APP=CUMME;DATABASE=CreditData

Why am I now allowed to edit, how to I get back to NOT being able to edit?

Thanks
Brian

 

[Sylvain Lafontaine]

For your ADO stuff and the opened connection that is kept open, I don't know
but for your problem with the saving of the password, you should use the
dbAttachSavePWD attribute; see:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20322239.html?qid=20322239

Notice that the solution proposed in this url may have a bug and that the
second line in the following extract:

If (tdf.Attributes And dbAttachSavePWD) = 0 Then
tdf.Attributes = dbAttachSavePWD
End If

should be replaced with:

If (tdf.Attributes And dbAttachSavePWD) = 0 Then
tdf.Attributes = tdf.Attributes OR dbAttachSavePWD
End If

or even better:

If ((tdf.Attributes And dbAttachSavePWD) <> dbAttachSavePWD) Then
tdf.Attributes = tdf.Attributes OR dbAttachSavePWD
End If

Search Google and see also:
http://www.mcse.ms/message1605499.html

 

[Brian]

Sylvain,
I mentioned the ADO connection stuff in case it may have an influence on the
odbc connection permissions.

The link to experts exchange wants you to pay for the solution.

I get an Invalid argument error when I do tdf.Attributes = tdf.Attributes
OR dbAttachSavePWD
I'm not sure of the order of the settings is it...
td.connect=strConnect
td.Attributes = tdf.Attributes OR dbAttachSavePWD
td.RefreshLink

What I want to do is that the tables are there but they were linked to
another server so I just want to change the connect string to point to the
new server.

Thanks
Brian

 

[Sylvain Lafontaine]

- The link to experts exchange wants you to pay for the solution.

Are-you sure? Did you look at up to the bottom of the HTML page? Here on
my side, I don't see any requrest for paying something and I never
registered myself with Expert-Exchange. However, searching for Google
should give you a lot of information about dbAttachSavePWD

I don't know more about your error because I don't use ODBC linked tables;
however, it's possible that the error in your case is because the constant
value of dbAttachSavePWD is not recognised, so maybe you should use its
numerical value (&H20000 or 131072 in decimal) instead.

 

[Brian]

Sylvain,
You are correct, I clicked on the button marked view solution next to the
description of the problem and that's where it told me I had to pay to see
the solution.
I did follow another link about dbAttachSavePWD and found another solution
that deletes and recreates the links and the security does seem to work
properly. I was wondering though why just setting the connect and
refreshing link does not work? It works for links to mdb files.

Brian

 

[Sylvain Lafontaine]

If you have found a solution that work, keep it this way. Don't expect to
much about security and ODBC linked tables. These are a kludge and you will
never get more than that.

 

[Ettore Pancini]

i recently got similar problems. the strange thing is we have applications running fine since a lot of time, which now exibihits security problems (linked tables which requires validation, errors while refreshing table attributes)
i wonder if some security patch has change some behaviour.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com