Very Computer

by **Rut** » Wed, 11 Jul 2001 04:38:43

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("ADODB.Connection")

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

Response.Write "Connection Opened!<BR>"
[this part works in both cases]

Set RS=Server.CreateObject("ADODB.Recordset")
RS.Open "Select * from map_gallery_primary_geographies", CN,3, 3

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

Response.Write "Value of the first field's record: " & RS(0)

TIA,

Ruth

by **Rut** » Thu, 12 Jul 2001 04:46:02

It turns out that if the owner of the table is not dbo, you need to
specify the owner when referencing the table. Thus my code should
have read: RS.Open "Select * from
WebAdmin.map_gallery_primary_geographies", CN,3, 3
if I was accessing it from the WebPublic login.

> 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("ADODB.Connection")

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

> Response.Write "Connection Opened!<BR>"
> [this part works in both cases]

> Set RS=Server.CreateObject("ADODB.Recordset")
> RS.Open "Select * from map_gallery_primary_geographies", CN,3, 3

> [this generates the "Invalid object name" error for the login that is
> not listed as owner in the Enterprise Manager]

> Response.Write "Value of the first field's record: " & RS(0)

> TIA,

> Ruth

Very Computer

SQL Server Logins and Web access

SQL Server Logins and Web access

SQL Server Logins and Web access