Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2441
  • Last Modified:

Copy One Database to Another

I have a database (let's call it XYZ) and want to copy it on a scheduled basis to another database called XYZ_Archive. I've tried using DTS and inevitably it fails, usually because of some PK and FK issues. I've tried both options of copying just 1) tables and views and 2) Copy Objects between SQL Servers.

I'm getting closer having limited the tables that I am copying, but now it errors out and tells me 'COPY SQL OBJECTS ERROR. The login already has an account under a different user name.'

Amy ideas as to why this is occurring?

What is the best way to accomplish this task. Am I going about it the wrong way?

1 Solution
Have you considered replication?

easiest way is to restore a backup.

You can also transfer using sql-dmo.
example at
DMO transfer

Your error is probably occurring because you are copying database users and the login ids are different on the target server.

If you can't do the backup-restore I would go for dmo.
Replication is quite a pain....DTS really sucks at trying to copy objects--hopefully it gets better in Yukon.  You might take a look at setting up log-shipping.  Really easy to implement and you just have a stored proc setup on the "archive" database that applies the logfiles on a pre-determined interval.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

What's the status of this question?
This error is because of SQL Server's script generation of the logins for a database.  You can replicate this in enterprise manager by selecting you database and right clicking under all tasks choose generate sql scripts.  On the options tab choose script database users, logins.  

The statement like this will cause your error:

if not exists (select * from dbo.sysusers where name = N'user' and uid < 16382)
    EXEC sp_grantdbaccess N'user'

if you take a look at the code for sp_grantdbaccess in the master database you will find that the following code is the error generation point:

        if (not user_sid(user_id(@name_in_db)) = @sid)
                return (1)

--- to solve the problem i usually make sure that i don't have any logins in the destination server and have everything created from scratch by the script generator (this usually works best in DTS on the copy objects)  

I have also found that if someone else other than the sa is the true database owner then this will cause your error.

try running this on the source database before running dts:

EXEC sp_changedbowner 'sa'
I also managed to reproduce it with one other scenerio....

SQL server automatically assigns the logged in user to the database alias dbo when you do a create database.

See if the login that your using to create the database in the destination (this would be the login you use in the second page of the Export Data dialog) already exists in the database your trying to copy as a user.

If so, copy all objects would see that login name as assigned rights, it will try to add that user again to the destination.  Since this user is already "dbo" you get the error that the user is already in the database with a differnet name.  Which is true.  If you open the SYSUsers table you will see the entry for dbo is not ID 1.  Try creating a new user on the destination server with sys admin rights and use this login for DTS when doing your copy.  

Ideally the script that is autogenerated by sql scripts should be this.. so if you want to fix it yourself you could...:

if not exists (select * from master.dbo.sysxlogins ml inner join dbo.sysusers su on ml.sid = su.sid where ml.name = N'user' and su.uid < 16382)

   EXEC sp_grantdbaccess N'user'

The key is that SQL server has built in functionality that inserts a user's SID into the sysusers table on creation -- the logged in user get the dbo spot, and the script generator doesn't take this into account, and will generate an error.  

I usually have a policy of having all DDL created by a seperate user who exists only as a sysadmin specifically for adminning the server and creating databases etc, and did not run into this problem until tonight.    Hopefully this covers enough scenerios to help anyone else looking for an answer.  

--going to sleep now - DahNight



Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now