ASP problem - Invalid object name 'dbo.tablename'

[Tom Bynum]

I've got a really strange ASP situation that driving me crazy! I'm sorry
that it sounds like a word problem, but these are the things I've tried.

Here's the layout:

IIS 5.0 running on Machine1
Sql Server 2000 running on Machine 2 (it uses both Windows Integrated and
SQL Server security )
Simple enough...

There's a database on Machine 2 (now think of that)... = "MyDb"
All the tables in that database are owned by the same user = "dbo"
There is a User Login setup for the database named "SQLUsers". (It's a Win
2000 domain group.)

My 3 test tables are named:
Test1 (has no explicit rights setup for Login = SQLUsers )
Test2 (has select, insert, update, delete rights for Login =
SQLUsers )
Test3 (has select, insert, update, delete rights for Login =
SQLUsers )

(Test1 and Test2 have been there a while, I just created Test3 on Friday)

Now let's get to this pesky ASP page:

The particular web in question is running only with "Windows integrated
Security". (Not Anonymous or Basic)
I've turned "Digest authentication..." on and off several times, doesn't
appear to matter.

I've tried two connect strings:
Method1 = conn.Open "Provider=SQLOLEDB.1;ServerName=SQL2KSERVER;Integr ated
Security=SSPI;Initial Catalog = MyDb"

Method2 = conn.Open "Provider=SQLOLEDB.1;Data Source=SQL2KSERVER;Integrated
Security=SSPI;Initial Catalog = MyDb"

Using "Windows Integrated Security" (which is what I want)

If I use Method1 to connect, I can successfully query both Test1 and Test2
tables...BUT NOT Test3. The returned IIS error is:

Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'dbo.Test3'.

If I change the site security to "Basic" and change to connect Method2...I
can query all three tables...(after that pesky login prompt)

It's driving me nuts, can anyone shed some light?

[Ray at]

I'd imagine that the it's connecting you to the default database for your
trusted user. You can either fully qualify your table names,
MyDB.dbo.Test3, change the default database for the user, or execute a "USE
MyDB" when you connect. Those are the things that I'd try anyway.

Ray at work

"Tom Bynum" <tbynum@argusinc.com> wrote in message
news:eWLJne8pDHA.2676@TK2MSFTNGP11.phx.gbl...

>> Method2 = conn.Open "Provider=SQLOLEDB.1;Data

Source=SQL2KSERVER;Integrated

> Security=SSPI;Initial Catalog = MyDb"
>
> Using "Windows Integrated Security" (which is what I want)
>
> If I use Method1 to connect, I can successfully query both Test1 and Test2
> tables...BUT NOT Test3. The returned IIS error is:
>
> Microsoft OLE DB Provider for SQL Server error '80040e37'
> Invalid object name 'dbo.Test3'.

[Tom Bynum]

Fully qualified database.owner.object did not work.
....besides, that's what the "Initial Catalog" directive is for in the
connect options...

For those just joining, here's the condensed version of the problem.

Given a SQL2000 server with Database, "MyDB" using "Windows Integrated
Security"

ON the IIS Server+Site:

With "NT integrated security"
If I write the connect string this way:
"Provider=SQLOLEDB.1;ServerName=SQL2KSERVER;Integr ated Security =
SSPI;Initial Catalog=MyDb"
I can query 2 out of 3 tables.

With "Basic" authentication
If I write the connect string this way:
"Provider=SQLOLEDB.1;Data Source=SQL2KSERVER;Integrated Security =
SSPI;Initial Catalog=MyDb"
I can query all 3 tables.

That's just nuts! It has to be something different about that last table
but I can't find it !

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:OmqyPi8pDHA.2528@TK2MSFTNGP10.phx.gbl...

> I'd imagine that the it's connecting you to the default database for your
> trusted user. You can either fully qualify your table names,
> MyDB.dbo.Test3, change the default database for the user, or execute a

"USE

> MyDB" when you connect. Those are the things that I'd try anyway.
>
> Ray at work
>
> "Tom Bynum" <tbynum@argusinc.com> wrote in message
> news:eWLJne8pDHA.2676@TK2MSFTNGP11.phx.gbl...

> >> Method2 = conn.Open "Provider=SQLOLEDB.1;Data

> Source=SQL2KSERVER;Integrated

> > Security=SSPI;Initial Catalog = MyDb"
> >
> > Using "Windows Integrated Security" (which is what I want)
> >
> > If I use Method1 to connect, I can successfully query both Test1 and

Test2

> > tables...BUT NOT Test3. The returned IIS error is:
> >
> > Microsoft OLE DB Provider for SQL Server error '80040e37'
> > Invalid object name 'dbo.Test3'.

>
>

[Ray at]

"Tom Bynum" <tbynum@argusinc.com> wrote in message
news:OYmhSP9pDHA.648@TK2MSFTNGP11.phx.gbl...

> Fully qualified database.owner.object did not work.
> ...besides, that's what the "Initial Catalog" directive is for in the
> connect options...

I have seen instances where Initial Catalog is ignored. Why? I don't know.
If I ever get around to updating MDAC, maybe I'll find out that's the cause.

And the other two options failed as well?

Ray at work

[Aaron Bertrand [MVP]]

Why are user rights applied differently across the three tables? If you
want "SQLUsers" the ability to see all three tables, then apply the same
permissions across all three tables.




"Tom Bynum" <tbynum@argusinc.com> wrote in message
news:OYmhSP9pDHA.648@TK2MSFTNGP11.phx.gbl...

> Fully qualified database.owner.object did not work.
> ...besides, that's what the "Initial Catalog" directive is for in the
> connect options...
>
> For those just joining, here's the condensed version of the problem.
>
> Given a SQL2000 server with Database, "MyDB" using "Windows Integrated
> Security"
>
> ON the IIS Server+Site:
>
> With "NT integrated security"
> If I write the connect string this way:
> "Provider=SQLOLEDB.1;ServerName=SQL2KSERVER;Integr ated Security =
> SSPI;Initial Catalog=MyDb"
> I can query 2 out of 3 tables.
>
> With "Basic" authentication
> If I write the connect string this way:
> "Provider=SQLOLEDB.1;Data Source=SQL2KSERVER;Integrated Security =
> SSPI;Initial Catalog=MyDb"
> I can query all 3 tables.
>
> That's just nuts! It has to be something different about that last table
> but I can't find it !
>
> "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> news:OmqyPi8pDHA.2528@TK2MSFTNGP10.phx.gbl...

> > I'd imagine that the it's connecting you to the default database for

your

> > trusted user. You can either fully qualify your table names,
> > MyDB.dbo.Test3, change the default database for the user, or execute a

> "USE

> > MyDB" when you connect. Those are the things that I'd try anyway.
> >
> > Ray at work
> >
> > "Tom Bynum" <tbynum@argusinc.com> wrote in message
> > news:eWLJne8pDHA.2676@TK2MSFTNGP11.phx.gbl...

> > >> Method2 = conn.Open "Provider=SQLOLEDB.1;Data

> > Source=SQL2KSERVER;Integrated

> > > Security=SSPI;Initial Catalog = MyDb"
> > >
> > > Using "Windows Integrated Security" (which is what I want)
> > >
> > > If I use Method1 to connect, I can successfully query both Test1 and

> Test2

> > > tables...BUT NOT Test3. The returned IIS error is:
> > >
> > > Microsoft OLE DB Provider for SQL Server error '80040e37'
> > > Invalid object name 'dbo.Test3'.

> >
> >

>
>

[Tom Bynum]

All this came about purely by accident:

The original ASP page was developed on another NT 4 box. So I was just
moving it over to a Win 2000 box using IIS 5.0
The database never moved. Still on the same box.

When it failed to connect to Table3 on the new box, I wondered "Can I
connect to anything over there?" So I picked a table...happened to be
Table1 (the one without any explicit rights). Pure luck of the draw...and
it worked!

So then I started examing user rights on tables...Come to find out the
SQLUSERS login only had explicit rights to 5 tables in the database. OK,
now the question is "Can I connect to one of those 5?"....Answer = Yes.

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:eYJC3Z9pDHA.2012@TK2MSFTNGP12.phx.gbl...

> Why are user rights applied differently across the three tables? If you
> want "SQLUsers" the ability to see all three tables, then apply the same
> permissions across all three tables.
>
>
>
>
> "Tom Bynum" <tbynum@argusinc.com> wrote in message
> news:OYmhSP9pDHA.648@TK2MSFTNGP11.phx.gbl...

> > Fully qualified database.owner.object did not work.
> > ...besides, that's what the "Initial Catalog" directive is for in the
> > connect options...
> >
> > For those just joining, here's the condensed version of the problem.
> >
> > Given a SQL2000 server with Database, "MyDB" using "Windows Integrated
> > Security"
> >
> > ON the IIS Server+Site:
> >
> > With "NT integrated security"
> > If I write the connect string this way:
> > "Provider=SQLOLEDB.1;ServerName=SQL2KSERVER;Integr ated Security =
> > SSPI;Initial Catalog=MyDb"
> > I can query 2 out of 3 tables.
> >
> > With "Basic" authentication
> > If I write the connect string this way:
> > "Provider=SQLOLEDB.1;Data Source=SQL2KSERVER;Integrated Security =
> > SSPI;Initial Catalog=MyDb"
> > I can query all 3 tables.
> >
> > That's just nuts! It has to be something different about that last

table

> > but I can't find it !
> >
> > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> > news:OmqyPi8pDHA.2528@TK2MSFTNGP10.phx.gbl...

> > > I'd imagine that the it's connecting you to the default database for

> your

> > > trusted user. You can either fully qualify your table names,
> > > MyDB.dbo.Test3, change the default database for the user, or execute a

> > "USE

> > > MyDB" when you connect. Those are the things that I'd try anyway.
> > >
> > > Ray at work
> > >
> > > "Tom Bynum" <tbynum@argusinc.com> wrote in message
> > > news:eWLJne8pDHA.2676@TK2MSFTNGP11.phx.gbl...
> > > >> Method2 = conn.Open "Provider=SQLOLEDB.1;Data
> > > Source=SQL2KSERVER;Integrated
> > > > Security=SSPI;Initial Catalog = MyDb"
> > > >
> > > > Using "Windows Integrated Security" (which is what I want)
> > > >
> > > > If I use Method1 to connect, I can successfully query both Test1 and

> > Test2

> > > > tables...BUT NOT Test3. The returned IIS error is:
> > > >
> > > > Microsoft OLE DB Provider for SQL Server error '80040e37'
> > > > Invalid object name 'dbo.Test3'.
> > >
> > >

> >
> >

>
>