ODBC+MFC; CRecordset::Update() syntax error or access violation

DBMS: SQL Server 2000 sp3 (and sp4)
Tool: VC++ 6.0 with the MFC
Connection: ODBC MDAC 2.8

Problem:
"Syntax error or access violation" error when the public role does not
select rights on the sysdatabases and the CRecorset::Update() is invoked.

The problem described below uses the Patient table as an example. This
problem
persist through the whole application whenever we try to update the data
using the
CRecorset::Update();

/////////////////////////////////////////////
class Patient : public CRecordset
{
public:
Patient(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(Patient)

long m_PATIENT_ID;
.....
CString m_PAT_FNAME;
CString m_PAT_LNAME;
public:
virtual CString GetDefaultConnect();
virtual CString GetDefaultSQL();
virtual void DoFieldExchange(CFieldExchange* pFX);

CString Patient::GetDefaultSQL()
{
return _T("[dbo].[PATIENT]");
}
///////////////////////////////////////////////////////////////////////////
//We are connecting to the database using ODBC (DSNless connection) TCP/IP

CString CONNECTSTRING_EX;
CDatabase *currDatabase;

CONNECTSTRING_EX.Format("DRIVER={SQLServer};DSN='';UID=MYDB_USER;PWD=Pwd;DATABASE=MYDB;" "SERVER=server1;Network=DBMSSOCN;Address=127.0.0.1,1456");

currDatabase->OpenEx( _T(CONNECTSTRING_EX), CDatabase::noOdbcDialog);


Patient *us = new Patient(currDatabase);
us->m_strFilter = "PATIENT_ID = 2" ;
us->Open(CRecordset::snapshot,NULL,CRecordset::none);

//The recordset opens fine and retrieves the right record.
//We want to update this record:

us->Edit();
us->m_PAT_FNAME = "Some_NAME";
us->Update();

/////////////// PROBLEM /////////////////////////////
Error Message: syntax error or access violation
///////////////////////////////////////////////////////

The user MYDB_USER is a member of the MYDB_USERS role that has select,
update, delete and insert rights on all users tables in
MYDB database.

This user is able to update the Patient table from SQLQuery Analyzer.

Everything was working fine until the select permission on the public role
were removed in all system tables in the master database
When we investigated this problem and we discovered that the lack of the
select permission for the public role for sysdatabases table in master
database is the cause of the error.

We were looking for the symilar problem report but we have not been able to
find anything.

It seems that the MFC recordset is trying to acceess the master database
sysdatabase table.
.

Relevant Pages

  • ODBC-MFC CRecordset::Update() Syntax error or access violation
    ... select rights on the sysdatabases and the CRecorset::Updateis invoked. ... //We are connecting to the database using ODBC (DSNless connection) TCP/IP ... Everything was working fine until the select permission on the public role ... permission for the public role for sysdatabases table in master database is ...
    (microsoft.public.vc.database)
  • Re: PUBLIC ROLE
    ... think of the Public role as being very much ... > If I create a database called "TEST" and make a user George database ... you grant those permissions to the public role. ...
    (microsoft.public.sqlserver.security)
  • Re: Public Role in SQL Server 2000 SP3
    ... The public role is a special database role to which every database user ... Captures all default permissions for users in a database. ... Hari is a database owner and he create a table customer. ...
    (microsoft.public.sqlserver.server)
  • Re: new userID can do all...
    ... Being in the public role in a database shouldn't give you access to do ... >>>> No server roles have been assigned. ... >>> Does he have administrative rights to the box ...
    (microsoft.public.sqlserver.security)
  • Re: Remove permissions in default Public Role?
    ... functionality. ... in a database or when connecting to a database server. ... You'd want to make sure you are not granting permissions to ... happening because of the Public Role in the Master db. ...
    (microsoft.public.sqlserver.security)