very frustrated....

[aspnet98]

I am migrating from a shared hosting plan sql server to my own sql server. I am not an admin on the shared plan and cannot use the copy database or transfer database feature....(anyone know a way to bypass that secuirty) Anyways, I have to use access project to import all of the sql objects to the new server...I created the uses the same way in sql server manager but when i copy the objects some in my shared db had different owners per table...not sure how that happened. i can't seem to get them all to have the same owner...then a lot of my views fail because of references...and my asp calls to the db fail because of dbo.tbltest or user.tbldetails etc.....it is a nightmare...anyone have any soultions to this? can i somehow copy the DB and all of the objects in full...i tried DTS and all the methods in their and it fails because of the owner reasons above???

very frustrated....

i was using the spCHANGE OWNER manually on each table to get the in sync with the hosted plan one but it will take weeks .....

 

[DrSql]

You can create a cursor to loop and execute sp_changeobjectowner where ownere <> dbo.
If you need further assistnace let me know.

Dr.Sql
Good Luck.

 

[Jamfool]

could try:

declare @sql nvarchar(4000)
declare @db  sysname ; set @db = db_name()
declare @u   sysname ; set @u = 'user'

set @sql ='SELECT ''EXEC sp_changeobjectowner '''''' + TABLE_SCHEMA + ''.'' +
TABLE_NAME  + '''''',''  + ''''''' + @u  + ''''''' FROM ' + @db + '.INFORMATION_SCHEMA.TABLES WHERE 
TABLE_NAME <> ''dtproperties'' 
AND TABLE_NAME <> ''sysconstraints''
AND TABLE_NAME <> ''syssegments''
AND TABLE_SCHEMA <> ''dbo''
' + ''
print @sql
exec master.dbo.xp_execresultset @sql,@db

 

[aspnet98]

thats for the response drsql and jamfool. i do need more help and am not sure what the code does that you posted?

Could you provide more insight?

 

[aspnet98]

jamfool that was a cool script, it did change the name for the tables i have. Will it mess anything up in the master DB that would poor my perfoprmance? i do not like messing around with a db this way but i have no choice and just wanted to make sure that the inner workings do not get messed up.

Now for all my views and stored procedures...can we do that same thing to map them to the new ownner?

 

[aspnet98]

the problem is the view code refers to OLDOWNER.TABLENAME. I have 500 views like that....How would I change the references in mass? I PRAY TO GOD I DO NOT HAVE TO MANUALLY DO THIS........

Any advice on that. Not sure that changing the owner of the view would help this because the select statements are what need to change........

 

[SQLSister]

Have you tried asking them to detach your database and physically give you a copy of the file? It's your database they should be willing to do this. Before you attach it yourself on your new server, read in BOL about orphaned users. If you set up the same users in advance, you might be able to avoid this problem and use the same users for objects in your new db.

Let this be a lesson to you. Alweays use dbo as the owner of objects. It makes moving things around so much simpler.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[aspnet98]

they said no way...I am doing it manually .............................Any other thoughts....this is a lesson to me like you said ..........