connection strings

I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work.

I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name.

My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation.

I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what???

Can't you just change the default schema of the user you use to login with through asp.net to the new schema?

|||

I'm not sure I understand what you are saying when you say change the schema of the user. I'm pretty sure the hosting account accesses via the user ASPNET via IIS and I also assume that Visual Web Developer 2005 accesses via my user account on the development machine, but I don't understand "user schema". Are we referring to access privileges?

|||

No I'm talking about the schema. When you say that you have to write owner.tablename to access a table that means that that table is in the schema called owner, which is also the default schema for the user with the same name. Normally the default schema for most database objects is dbo. So when you write a select statement like "select * from sometable" it's really the same as writing "select * from dbo.sometable". In sql server you have the option to change the default schema from dbo to something else. Say that all the database objects you want to work on are in a schema called "aspnet". If the default schema of the user you want to access these objects with is not "aspnet", you would have to write all your queries like "select * from aspnet.sometable". However if you change the default schema for that user to "aspnet" you can now write "select * from sometable" with the same result. The way to change schema is to open the properties sheet for the user and change the default schema. It's right there on the main page.

Hope it helps

|||

I tried

CREATE SCHEMA AUTHORIZATION usernamefromisp

GRANT ALL TO usernamefromisp

then changed to point to the restored production database and got the same error message that objects (tables) do not exist.

|||

Hi,

From your description, it seems that you are not able to select the tables in your database which restored from a backup file, right?

As you have mentioned, if you added the database owner in your select statement, it works well. So the cause of the problem is that the database(all datatables) owner is not dbo which is the default owner in your database. So while you are writing select statements without database owner, the system regard it as dbo.Object by default.

In your case, the system can't find the dbo.YourObject since the owner of YourObject is other than dbo. Now you have two workarounds, first, just write the select statement with the object owner. The second way is to change the object owner to dbo.

For how to change, you may refer the following link:
http://msdn2.microsoft.com/en-us/library/ms190909.aspx

Thanks.

|||

Thanks,

Your reply didn't quite fix the problem, but put me on the right track. I didn't give you enough definition because it was not just the database ownership at issue, but also many of the individual tables within the database. I used the sp_changeobjectowner on each of them and the problem cleared.

Thank you for your help.