k3s.live

Based on the IT journey of Michael Rickert

SQL Error ‘Copy SQL Database user exists’

The Issue:

When attempting to copy or replicate a mySQL database you recieve the error ‘Copy SQL database user exists’

The Cause:

When copying the SQL database, if the user no longer exists but is still attached to the database security groups, an error will occur when attempting to copy the database and its files.
The Fix:

There are two ways to solve this issue, the first way simply deletes the user account causing the error, the second way will keep the user account during the copy.

First way:

  1.        Lauch mySQL manager and run the following SQL query:
    Use [yourdatabasenamehere]
  2. Go
  3. Exec sp_dropuser ‘usercausingerror’
  4. Go
  5. Re-run the database copy command and the error will no longer occur

Second way:

  1. Launch mySQL manager and run the following SQL query:
  2. Use [yourdatabasenamehere]
    Go<
    sp_change_users_login @Action=’Report’
    Go

  3. The query will list all ‘estranged’ users in the report in the results box, each of the users listed will have to either be dropped or linked to a new account
  4. To link the user to an account enter the following SQL query:
  5. Use [yourdatabasenamehere]
    Go
    Exec sp_change_users_login ‘Auto_Fix, ‘usercausingerror’, NULL, ‘passwordhere’
    Go

  6. The above syntax will link the user account entered to a new non-deleted user account that can then be copied, just run the database copy command again.

Leave a Reply