Can read but cannot update

问题:

Problem: Any attempt to update the DB is denied in SQL Server 2008 R2 using SQL in classic ASP page.

In a nutshell, can read, but can't update/write to DB using SQL in ASP page

Error message:

Microsoft OLE DB Provider for SQL Server error '80040e4d'

Login failed for user

It has taken me more than 6 hours to troubleshoot, but still no answer

Things Checked

1. Password for user is correct. Double checked. Tested a wrong password and resulted that I can't read from DB.

2. When user password is correct, can read from database. Can use SQL to read from DB. Just cannot update using SQL.

3. db_datareader & db_datawriter is checked for the user

4. Checked the SQL Server log - error 18456 severity 14 state 8. This is the password mismatch. But I am very sure the password is correct, because I can read from DB. It is ONLY when the SQL attempt to write to DB, it is denied.

Hints

• Is the dbo required to have db_datareader & db_datawriter checked as well? I am not able to checklist that in SQL server Management studio

• Anything about the schema I need to do?

Anybody help. Thanks a lot/

It works perfectly and correctly in another site using SQL Server 2000. When I migrate to SQL Server 2008 R2, this is the problem.

AA Jaz

回答1:

Looks like your login to the SQL Server have only the read permissions on the database (/table / server) and it dont have the write permissions, the dbwriter and dbreader are the roles in SQL Server 2000, in 2008 it all changed you have add roles to your login.

Please try to give the login all the permissions, as below

GRANT ALL ON <your_table> to <your_login>

回答2:

please try this db from sql server management studio to update any table by query in query analyzer if it does not work then check table permission or login user permission