ERROR: Invalid Object Name

[Guest]

Hi,

I'm currently trying to retrieve information from a specific table within a
SQL Database using the following code:

Dim oSqlCommand As New SqlClient.SqlCommand
oSqlCommand.CommandType = CommandType.Text
oSqlCommand.Connection = mSqlConnection
oSqlcCommand.CommandText = "SELECT * FROM SOMETABLE"

Dim oSqlReader As SqlClient.SqlDataReader
oSqlReader = oSqlCommand.ExecuteReader
oSqlCommand.Dispose()

Connection to the SQL Database is performed like this :

strConnection = "Driver={SQL Server}" & _
";Server=" & mServerName & _
";Database=" & mDatabaseName & _
";Uid=" & mUserID & _
";Pwd=" & mPassword & ";"

mSqlConnection = New SqlClient.SqlConnection(strConnection)
mSqlConnection.CreateCommand()

While performing the query with the SOMETABLE owner mUserID, there is no
problem accessing the SOMETABLE information.

Therefore, while performing the query with the Database System Administrator
and DB owner mUserID, I'm unable to access the SOMETABLE information getting
the Invalid Object Name 'SOMETABLE' error message.

If I tried to perform the same thing using EXCEL query, the SOMETABLE
information could be accessed by both mUserID which is not the case with my
application.

It seems that only the SOMETABLE owner could access the table. Why the
Database System Administrator and DB owner could not access the SOMETABLE? Am
I doing something wrong while connecting to the Database? Do I have to change
permissions? Why is it working with EXCEL and not with my application?

Thanks

 

[W.G. Ryan eMVP]

Am I understanding the problem correctly? " While performing the query with
the SOMETABLE owner mUserID, there is no

problem accessing the SOMETABLE information.

Therefore, while performing the query with the Database System Administrator
and DB owner mUserID, I'm unable to access the SOMETABLE information getting
the Invalid Object Name 'SOMETABLE' error message."

Can you see it as the owner with mUserID?

BTW, Excel doesn't have the permissioning that Sql Server does - so that
could easily account for the difference.

 

[Guest]

Hi W. G. Ryan,

To make sure you understand the problem correctly, I'll try to give you a
better description.

Let say that I have the following DSN files:

For user1

[ODBC]
DRIVER=SQL Server
UID=purreq
PWD=password
Network=DBMSSOCN
DATABASE=DB
WSID=COMPUTER90
APP=Microsoft Office 2003
SERVER=192.168.0.19

For user2:

[ODBC]
DRIVER=SQL Server
UID=privuser
PWD=password
Network=DBMSSOCN
DATABASE=DB
WSID=COMPUTER90
APP=Microsoft Office 2003
SERVER=192.168.0.19

Then, assume that the table I want to access on SERVER is named ACCOUNT. The
owner of the ACCOUNT table is UID-privuser
Both user have full permission on ACCOUNT (privuser) table.

Permit in Database role for UID=purreq is public and db_owner
Permit in Database role for UID=privuser is public and db_datareader

While using both DSN files within Microsoft Excel to perfom Database Query,
no problem is encountered. Data could be fully retrieved from the ACCOUNT
table.

While using my application, by connecting to the SQL server using the
following connection string:
"FileDsn=C:\Program Files\Common Files\ODBC\Data Sources\userX.dsn"

Only UID=privuser (user2.dsn) who is the owner of the table could access the
ACCOUNT table. Otherwise UID=purreq (user1.dsn) got the 'Invalid Object Name
'ACCOUNT'' error message while trying to perform the ExecuteReader() method.

Thanks for your support and if you need further information, just let me know.

F. Chouinard