Security and SQL Server

[gharabed]

I have an Access app that front-ends a SQL Server database. I am trying to revoke privileges for users to update records into certain tables and no matter how I set the privileges through Enterprise Manager I am still able to update records when I go through Access. If I go in through say isql, the security holds. Does Access bypass security in SQL Server somehow?

 

[cmmrfrds]

How are you accessing the tables in sql server? through DAO, linked table using ODBC, or ADO? Is sql server setup with both NT and sql server authentication? How are you providing the login information to sql server from Access? IS the user's login NT or sql server?

 

[Junior1544]

another thought... check the public access to the tables... and that the public account can't do any thing to those tables that you wouldn't want any one in the world to be able to do...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[gharabed]

Public does not have access at all. In answer to how I am accessing the tables...I am doing it via ODBC using the native Access data editing capabilities when "opening" a table. SQL Server is set up for both NT and sql server authentication and I've tried ODBC connections using both NT and sql server authentication. Again, the login information is being provided via an ODBC DSN. As I said, if I am using the SQL Server authentication, if I log in through isql, I can't update an existing record because I don't have privileges. If I access the table through Access (link to table via ODBC) and open it, then I can update existing records. Strange.

 

[Fflyer]

WOW! You have just the problem that I am trying to create. I want a handful of people to be able to update the SQL yet the majority of people will be using a read-only ASP utility. If you are having difficulty getting this turned off, maybe it won't be hard to allow it.

Have you discovered any switches that were turned on/off that allowed someone to edit the linked SQL table or is that the scenario you are describing?

Thank you,

Chad

 

[gharabed]

I didn't set any switches. Apparantly by default, Access will bypass any security (table access privileges to SQL Server users) put in place by SQL Server when using an ODBC connection. I didn't set any flags on the Access side at all.

Greg