Read Only ODBC to SQL SERVER 2005

I have a Login that I setup in SQL Server 2005. The only database roles given to the login are db_datareader and public. On SQL 2000 these permissions are sufficient to allow me to setup a read only ODBC connection using this login and creating an import table in Access. When using the login in SQL 2005 and creating an import table, I am able to edit the underlying data, something I am not able to do with a SQL 2000 ODBC connection. Is there something in SQL 2005 that I am unaware of that would allow this login to modify (update) the table?

Assuming you are using SQL Server Authentication, I'd just double check the roles that account is a member of, and what permissions those roles have on the table in question.

If you are using Windows Authentication, make sure your account is NOT a member of the administrators group on the server. If it is, it automatically becomes part of the Server Administrator's role and can basically do anything.

When I run into these things I often find it's something silly that I overlooked. Having another person look at things for you will often help catch the things you miss.

Good Luck

|||

We have 3 peoople looking at it, one of us has exprience going back to SQL 6.5, but no one can figure it out, lol. We are using SQL Auth and we even tried to deny update rights to the user on the table and take away all grant rights. As soon as we connect to a SQL 2005 table in Access using the ODBC, the user can edit whatever. It is strange because with the exact same permissions in 2000, the user is denied the access and it acts like we expect. In 2005, it is just a free for all. I know it is something silly and we are all going to slap our foreheads when we figure it out

|||

We had to give the user db_denydatawriter permissions. It is strange that we had to explicitly give these permissions when we didn't have to do it in SQL 2000, but it worked.

|||

Just a random thought: Does the schema assigned to the sql server login in question match the name of the login? What schema owns the table in question and what schema is the user in?

|||

dbo

|||

I think that may be your answer. I would try creating a seperate schema for the user in question and disassociate the dbo schema from the user.