Help! SQL Server error - [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name ...

[FreeOperator]

Dear all,

On Win2000 server with SP3, I am trying to access a SQL Server 7.0
database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO
2.7. In SQL Server Enterprise Manager, there is a login named "Tester".
In its property window, NO "Server Roles" was assigned but its
"Database Access" was set to "TestDB". This login was also made as the
user of "TestDB" with "public", "db_datareader" and "db_datawriter"
selected as its "Database role membership". All the tables I am trying
to access in "TestDB" were created under "Tester".

My code is like:

Set conn = New ADODB.Connection
conn.Open "DSN=TestDSN;UID=Tester;PWD=test"

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = SQL

set rs = cmd.Execute()

If I set the SQL to something like "SELECT * FROM tbl_test", I always
get an error of "-2147217865" saying "[Microsoft][ODBC SQL Server
Driver][SQL Server] Invalid object name tbl_test". If I set the SQL to
"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyone
please kindly advise why the first SQL is not working? Or in other
words, why must I prefix the table name with its owner while the DB
connection is already made under that owner name? Thanks in advance.

Tracy

[Dan Guzman]

I likely cause is that your ODBC DSN is setup for integrated security
instead of SQL authentication. The user id in the connection string is then
ignored and the connection is made using Windows authentication. The object
isn't found because the current user isn't 'Tester'.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"FreeOperator" <an******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Dear all,

On Win2000 server with SP3, I am trying to access a SQL Server 7.0
database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO
2.7. In SQL Server Enterprise Manager, there is a login named "Tester".
In its property window, NO "Server Roles" was assigned but its
"Database Access" was set to "TestDB". This login was also made as the
user of "TestDB" with "public", "db_datareader" and "db_datawriter"
selected as its "Database role membership". All the tables I am trying
to access in "TestDB" were created under "Tester".

My code is like:

Set conn = New ADODB.Connection
conn.Open "DSN=TestDSN;UID=Tester;PWD=test"

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = SQL

set rs = cmd.Execute()

If I set the SQL to something like "SELECT * FROM tbl_test", I always
get an error of "-2147217865" saying "[Microsoft][ODBC SQL Server
Driver][SQL Server] Invalid object name tbl_test". If I set the SQL to
"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyone
please kindly advise why the first SQL is not working? Or in other
words, why must I prefix the table name with its owner while the DB
connection is already made under that owner name? Thanks in advance.

Tracy

[FreeOperator]

Hi Dan,

Thank you for your kind reply, but I've picked "SQL authentication" for
my system ODBC DSN. I guess It must be something wrong with the user
permission settings in SQL Server 7.0. Just don't know what it is :-(

Thanks,
Tracy

[Dan Guzman]

Is your SQL Server configured to allow both SQL Server and Windows? This
will also force Windows authentication instead of SQL auth.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"FreeOperator" <an******@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Hi Dan,

Thank you for your kind reply, but I've picked "SQL authentication" for
my system ODBC DSN. I guess It must be something wrong with the user
permission settings in SQL Server 7.0. Just don't know what it is :-(

Thanks,
Tracy

[Erland Sommarskog]

FreeOperator (an******@yahoo.com) writes:

If I set the SQL to something like "SELECT * FROM tbl_test", I always
get an error of "-2147217865" saying "[Microsoft][ODBC SQL Server
Driver][SQL Server] Invalid object name tbl_test". If I set the SQL to
"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyone
please kindly advise why the first SQL is not working? Or in other
words, why must I prefix the table name with its owner while the DB
connection is already made under that owner name? Thanks in advance.

In addition to Dan's post, run a "SELECT USER" to see who you really
are.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[FreeOperator]

Hi Dan,

Thank you again for your kind reply. My SQL Server authentication has
been set to both NT and SQL server, so it shouldn't be a problem ...

Tracy

[Dan Guzman]

You might try connecting with Query Analyzer using the Tester login and
running the SELECT USER query Erland suggested.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"FreeOperator" <an******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi Dan,

Thank you again for your kind reply. My SQL Server authentication has
been set to both NT and SQL server, so it shouldn't be a problem ...

Tracy

[FreeOperator]

Thanks Dan, thanks Erland. "SELECT USER" sounds like a good idea. I'll
implement a test function in my VB DLL to do it. Since the problem
server is hosted by another company and it will take them days
(considering the easter holiday) to update my DLL and run the test for
me. Will let you know the result when I get it.

Thanks again,
Tracy

[Dan Guzman]

The VBScript below should provide the same results as your test app as long
as the connection string is the same. Perhaps a script version can expedite
the test.

Dim SQLConn, rs
Set SQLConn = CreateObject("ADODB.Connection")
SQLConn.Open "DSN=TestDSN;UID=Tester;PWD=test"
Set rs = SqlConn.Execute("SELECT USER")
MsgBox Rs.Fields(0)
rs.Close
SQLConn.Close

--
Hope this helps.

Dan Guzman
SQL Server MVP

"FreeOperator" <an******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Thanks Dan, thanks Erland. "SELECT USER" sounds like a good idea. I'll
implement a test function in my VB DLL to do it. Since the problem
server is hosted by another company and it will take them days
(considering the easter holiday) to update my DLL and run the test for
me. Will let you know the result when I get it.

Thanks again,
Tracy

[FreeOperator]

Hi Dan and Erland,

Finally got the result of my test script. "SELECT USER" reveals that
the DB user is "dbo" instead of "Tester" although my ODBC connection
string explicitly specifies the user to be "Tester". That's really
weird. Maybe I need to run "SETUSER Tester" directly after the DB
connection is made to make sure the user is correct. Still don't know
what goes wrong there :-(

Thanks,
Tracy

[John Bell]

Hi

Looks like Tester either owns the database or has sysadmin privileges (which
they probably should not have!). sp_changedbowner can change the database
ownership, sp_dropsrvrolemember can be used to remove the login from the
server role.

John

"FreeOperator" <an******@yahoo.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

Hi Dan and Erland,

Finally got the result of my test script. "SELECT USER" reveals that
the DB user is "dbo" instead of "Tester" although my ODBC connection
string explicitly specifies the user to be "Tester". That's really
weird. Maybe I need to run "SETUSER Tester" directly after the DB
connection is made to make sure the user is correct. Still don't know
what goes wrong there :-(

Thanks,
Tracy