SQL Logins and Web Access

[Ruth]

I have created 2 SQL logins for use in connecting to an SQL server database (v7) from my web server. My idea is that one login (WebAdmin) would be assigned the db_owner role and one login (WebPublic) db_datareader and db_datawriter roles.

However, I have run into a problem. Only one of the logins works. The other generates an "Invalid object name" error. The login that works is the login that is listed as the owner in the SQL Server Enterpise Manager Tables pane. Even if the other login has a db_owner role in the user properties box.

Can anyone tell me what is going on?

My code looks something like this:

<%
Set CN=Server.CreateObject(&#34;ADODB.Connection&#34

CN.Open &#34;Driver={SQL Server};SERVER=<ip address>;UID=<either WebAdmin or WebPublic>;PWD=<appropriate password>

Response.Write &#34;Connection Opened!<BR>&#34;
[this part works in both cases]

Set RS=Server.CreateObject(&#34;ADODB.Recordset&#34
RS.Open &#34;Select * from map_gallery_primary_geographies&#34;, CN,3, 3

[this generates the &#34;Invalid object name&#34; error for the login that is not listed as owner in the Enterprise Manager]

Response.Write &#34;Value of the first field&#39;s record: &#34; & RS(0)

[sanjay]

Hi
check for the ownership of the object ,i mean who owns that object .I might be wrong but try referencing the table using the table owner.eg

select * from Select * from dbo.map_gallery_primary_geographies

good luck

sanjay

------------
Ruth at 7/9/01 3:33:58 PM

I have created 2 SQL logins for use in connecting to an SQL server database (v7) from my web server. My idea is that one login (WebAdmin) would be assigned the db_owner role and one login (WebPublic) db_datareader and db_datawriter roles.

However, I have run into a problem. Only one of the logins works. The other generates an &#34;Invalid object name&#34; error. The login that works is the login that is listed as the owner in the SQL Server Enterpise Manager Tables pane. Even if the other login has a db_owner role in the user properties box.

Can anyone tell me what is going on?

My code looks something like this:

<%
Set CN=Server.CreateObject(&#34;ADODB.Connection&#34

CN.Open &#34;Driver={SQL Server};SERVER=<ip address>;UID=<either WebAdmin or WebPublic>;PWD=<appropriate password>

Response.Write &#34;Connection Opened!<BR>&#34;
[this part works in both cases]

Set RS=Server.CreateObject(&#34;ADODB.Recordset&#34
RS.Open &#34;Select * from map_gallery_primary_geographies&#34;, CN,3, 3

[this generates the &#34;Invalid object name&#34; error for the login that is not listed as owner in the Enterprise Manager]

Response.Write &#34;Value of the first field&#39;s record: &#34; & RS(0)

[Ruth]

Exactly right sanjay. Thanks! When I put the table owner in front of the table name it worked. It turns out that when the owner is dbo, you don&#39;t need to include the owner name in front of the table. So my ultimate solution is to make sure that dbo is the owner for all my tables. Thanks again.


------------
sanjay at 7/9/01 3:49:27 PM

Hi
check for the ownership of the object ,i mean who owns that object .I might be wrong but try referencing the table using the table owner.eg

select * from Select * from dbo.map_gallery_primary_geographies

good luck

sanjay

------------
Ruth at 7/9/01 3:33:58 PM

I have created 2 SQL logins for use in connecting to an SQL server database (v7) from my web server. My idea is that one login (WebAdmin) would be assigned the db_owner role and one login (WebPublic) db_datareader and db_datawriter roles.

However, I have run into a problem. Only one of the logins works. The other generates an &#34;Invalid object name&#34; error. The login that works is the login that is listed as the owner in the SQL Server Enterpise Manager Tables pane. Even if the other login has a db_owner role in the user properties box.

Can anyone tell me what is going on?

My code looks something like this:

<%
Set CN=Server.CreateObject(&#34;ADODB.Connection&#34

CN.Open &#34;Driver={SQL Server};SERVER=<ip address>;UID=<either WebAdmin or WebPublic>;PWD=<appropriate password>

Response.Write &#34;Connection Opened!<BR>&#34;
[this part works in both cases]

Set RS=Server.CreateObject(&#34;ADODB.Recordset&#34
RS.Open &#34;Select * from map_gallery_primary_geographies&#34;, CN,3, 3

[this generates the &#34;Invalid object name&#34; error for the login that is not listed as owner in the Enterprise Manager]

Response.Write &#34;Value of the first field&#39;s record: &#34; & RS(0)