How to lock out Update capability from Access 1

[resdevlabs]

We have a SS2k database and want to be able to allow a PowerUser to link to tables using Access. I have created a ReadOnly role that has Select rights to all required tables and Execute rights to only those procedures that perform Selects. I then created a new login id and granted that id the ReadOnly role.

When I login to Query Analyzer using the read only id, I cannot perform Insert, Update or Delete. However, when I link to a table in Access using the id, I am able to update data in the tables.

Authentication on the database is set to "SQL Server and Windows". I created a new ODBC DSN for the read only id and set the authentication to SQL Server with a login id/pwd.

Another curious item is that when I link to a table in Access, it does not ask for a password.

What have I missed or setup incorrectly? Any help in getting to the bottom of this will be greatly appreciated.


Rob
----------------------------------------------------------
There are 10 types of people in this world.
Those who understand binary, and those who don't.

 

[mrdenny]

If I remember correctly, when you first setup the link, it asks you which server, and has a checkbox for trunsted login.

You have to uncheck this to enter a username and password.

When the user makes the updates in the access frontend, are you sure that those changes are being pushed to the SQL DB? They might just be getting made in access, and not sent back.

I remember this being tricky back when I did something like this a few years ago.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[resdevlabs]

Denny, thanks for the reply.

Client OS: Win2K Pro
Server OS: Win2K

There are only two options that I can choose for authentication when setting up the ODBC connection - 1) Win NT authentication, 2)SQL Server authentication. The help says that NT authentication sets up a trusted connection and SQL Server authentication does not. The only other check box on the setup is "Connect to SQL Server to obtain default settings...".

The updates are being pushed to the database from Access. I verified this in the application and Query Analyzer.

I also removed all ODBC DSNs to this particular server and re-added the one with the read-only id. I get the same resutls.

Any suggestions will be greatly appreciated.


Rob
---------------------------------------------------------------
There are 10 types of people in this world.
Those who understand binary, and those who don't.

 

[mrdenny]

If memory servs there is a place in Access it self that you have to also set this up. Log into the database with Access, then do a sp_who on the SQL Server with QA. This will tell you which account is being used. Sounds like NT Permissions are being passed.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[resdevlabs]

I can't find anywhere in Access to set authentication. However, I have found a few interesting things out about the setup of our SS2k instance. I removed my login id from the database to try to force the connection to use the read only id I setup. I then tried to connect to the database using QA and Windows authentication. To my surprise, it worked. So after digging around some more, I found that I was able to connect because the BUILTIN\Administrators windows group is setup as a user with sa priviledges.

This seems like a risky setup. Any user granted admin priviledges to their workstation will have sa rights in the database (If I understand things correctly). I tried using Enterprise Manager to remove access to a particular database for this user, but I get the following error message:

Error 15405: Cannot use the reserved user or role name 'dbo'.

Am I correct in thinking BUILTIN\Administrators is a security risk or should I leave it? If I should remove it, how do I do that?

Thanks for all the input.


Rob
---------------------------------------------------------------
There are 10 types of people in this world.
Those who understand binary, and those who don't.

 

[mrdenny]

"Any user granted admin priviledges to their workstation will have sa rights in the database"; close. It's anyone who has admin privilegdes on the server will have sa rights in the database.

There is no need to remove the BUILTIN\Administrators.

I'm trying to remember where the settings are. It might be when you setup the linked tables. I don't have access installed, so I can't go and look at it.



Denny

--Anything is possible. All it takes is a little research. (Me)

 

[resdevlabs]

Thanks for the info, Denny. I'll just use a different account for testing the read only functionality.


Rob
---------------------------------------------------------------
There are 10 types of people in this world.
Those who understand binary, and those who don't.

 

[mrdenny]

no problem.

Denny

--Anything is possible. All it takes is a little research. (Me)