Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: DTS from Hosting

    I am trying to export from an SQL Server on a hosting company to my own computer using DTS. I'm new to SQL Server manipulations like this so please go easy on me...

    I can't seem to manage to do this properly - If I select "Copy tables & data only" it will not copy the identity constraints and primary keys. If I select "copy all objects" it starts giving me permission issues which I cannot seem to resolve, because I guess the tables on the hosting server are owned by my username up there, but when they come to my computer they are orphant?

    Isn't there an easy way to do this? It's a very simple database, tables and that's it, not even any stored procedures.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Ah , you have made a common mistake of having - you - rather than dbo owning objects.

    I would export all the data to the new machine like you are doing, then change ownership on EVERY object to be dbo.

    Yes, its long winded and a hassle....but this shows the reason why its so easy to have broken ownership chains if dbo doesnt own all objects.


    Cheers,

    SG

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    Thanks - I have actually done that meanwhile and it solved the problem.
    However - I have no control under what user the objects are created in the remote database as it is hosted - and I have to login in the user they supplied me, therefore all objects will be created under that user name.

    Or am I mistaken? Can I still override that and force the remote DB to create new objects under dbo?

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    The user dictates the ownership of the object.

    Unless you manually run a script to change owner to dbo - or - the user is sa level ( NOT recommended!!!! )....

    Cheers,

    SG.
    Last edited by sqlguy7777; 11-28-04 at 23:32.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •