ASP.net insists on using dbo. to call aspnet_* stored procedures

[Pedro Vera]

I am helping somebody setup one of the asp.net starter kits. I
converted it from sql express to sql server with no real issues, and I
got it running local perfectly.

On my first attempt to run it remotely I noticed that a lot of the
queries had dbo. hardcoded and that SQL server on the remote server
was not allowing my sql account access to these. I removed all
mentions of dbo. and again it still runs local. I have posted it to
the remote server once again, but I still get errors like this:

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'

Now, the common sense answer here is that the remote database was not
primed properly with:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

But this is not true. The remote db was primed properly and I can run
aspnet_* stored procedures from enterprise manager logged in using the
same account used in the connection string.

Any idea how I can go around this? My worst case scenario is to grab a
clean copy of the starter kit and instead of removing dbo., changing
it from dbo. to mysqlusername. and see if that helps.

Thanks,

Pedro Vera
http://veraperez.com

 

[Cowboy \(Gregory A. Beamer\)]

dbo is the schema used in SQL Server and, even if removed from queries, is
technically still there. Unless, of course, you have created a new schema
and own the objects in question. It sounds more like a permissions error or
connectivity problem than a dbo problem.

Is SQL Server on the same box as your application? If not, turn on the SQL
Browser service. If this is a default install, you may also have to turn on
a protocol other than Managed Local (or whatever it is called). TCP/IP is a
good one to turn on in most cases; you can also try named pipes.

 

[Pedro Vera]

Thanks so much for the almost instant reply!

Here is a bit more info:

Local version:

XP Pro SP2, SQL Server 2000 developer in same machine. The connection
string is setup to use the sa login and password. Everything works
peachy.

Remote version:

2003 server, no idea SP. SQL Server 2000 in a different machine. The
connection string is setup to use the sql login assigned to the
hosting account, call it "mysql_login". At least the connection string
works, because I am not getting a bad connection string error.

Here is what I don't understand. I would assume that if I am user
mysql_login, and I create an object, then if I use that login then
dbo.myobject should be the same as mysql_login.myobject, right? In my
usual projects at work I have not run into this issue, so I should
start checking if maybe that host has screwed up the sql server
permissions.

Thanks,

Pedro Vera
http://veraperez.com

 

[Mark Fitzpatrick]

dbo.myobject should be the same as mysql_login.myobject, right?

No. They are technically two different objects. You could have a table,
dbo.mytable and it will be completely different from mysql_login.table. Even
if they are technically identical they are not because each exists within
the particular schema.

The easiest way to get around this is to use the sp_changeobjectowner stored
procedure which will let you change the object ownere for any object. You
could to:

sp_changeobjectowner 'myprocedure','dbo' to change it to the database owner
to dbo on the object myprocedure.

 

[Juan T. Llibre]

re:
!> dbo.myobject should be the same as mysql_login.myobject, right?

No. mysql_login is one account and the dbo is another account,
unless you use the changedbowner stored procedure to change
database ownership from dbo to mysql_login.

Try this in the query analyzer :

EXEC sp_changedbowner 'mysql_login'

That will, effectively, make the mysql_login the dbo and should end your problems.

 

[Juan T. Llibre]

re:

No. They are technically two different objects.

Correct, as explained in the reply just sent before seeing yours.

re:

The easiest way to get around this is to use the sp_changeobjectowner stored procedure

The changedbowner stored procedure will work, too :

EXEC sp_changedbowner 'mysql_login'