How to copy a database to different server ?

[TimH]

Hi,

This should be a relatively simple question (and answer !) :-)

I would like to copy a database from one server to another and have created a backup file of the database in question and copied it to the new server. I then restored the database, no problem.

When I try to create a standard SQL user called 'DBTest' (which was on the original server and had dbo priviledges to the database) it works but as soon as I try to permission this user to the newly restored database I get the error:

"Error 15023: User or role 'DBTest' already exists in the current database"

The user DBTest does not exist in my newly restored database but does exist in the publi role in that database. I am not allowed to delete this user from the public role (option greyed out). Help !!!

Thanks,

TimH

[Prychan]

When you restore the database from a different server, the SUID is different. So you need to drop the user and recreate it.

------------
TimH at 7/5/01 8:11:02 AM

Hi,

This should be a relatively simple question (and answer !) :-)

I would like to copy a database from one server to another and have created a backup file of the database in question and copied it to the new server. I then restored the database, no problem.

When I try to create a standard SQL user called 'DBTest' (which was on the original server and had dbo priviledges to the database) it works but as soon as I try to permission this user to the newly restored database I get the error:

"Error 15023: User or role 'DBTest' already exists in the current database"

The user DBTest does not exist in my newly restored database but does exist in the publi role in that database. I am not allowed to delete this user from the public role (option greyed out). Help !!!

Thanks,

TimH

[TimH]

The problem I had was that the user did not exist in the database or the Security/Logins menu, but he did exist in the public database role of the new database. I cannot delete the user from the public role as he doesn't exist.
Catch 22 !!



------------
Prychan at 7/5/01 9:43:55 AM


When you restore the database from a different server, the SUID is different. So you need to drop the user and recreate it.

------------
TimH at 7/5/01 8:11:02 AM

Hi,

This should be a relatively simple question (and answer !) :-)

I would like to copy a database from one server to another and have created a backup file of the database in question and copied it to the new server. I then restored the database, no problem.

When I try to create a standard SQL user called 'DBTest' (which was on the original server and had dbo priviledges to the database) it works but as soon as I try to permission this user to the newly restored database I get the error:

"Error 15023: User or role 'DBTest' already exists in the current database"

The user DBTest does not exist in my newly restored database but does exist in the publi role in that database. I am not allowed to delete this user from the public role (option greyed out). Help !!!

Thanks,

TimH

[Venu]

Tim,

Run the below Sp on the new server where u have restored the database..

Sp_change_Users_login 'Auto_FIX','DBTest'

Hope this helps,
Good luck

Venu

------------
TimH at 7/5/01 9:49:21 AM

The problem I had was that the user did not exist in the database or the Security/Logins menu, but he did exist in the public database role of the new database. I cannot delete the user from the public role as he doesn't exist.
Catch 22 !!



------------
Prychan at 7/5/01 9:43:55 AM


When you restore the database from a different server, the SUID is different. So you need to drop the user and recreate it.

------------
TimH at 7/5/01 8:11:02 AM

Hi,

This should be a relatively simple question (and answer !) :-)

I would like to copy a database from one server to another and have created a backup file of the database in question and copied it to the new server. I then restored the database, no problem.

When I try to create a standard SQL user called 'DBTest' (which was on the original server and had dbo priviledges to the database) it works but as soon as I try to permission this user to the newly restored database I get the error:

"Error 15023: User or role 'DBTest' already exists in the current database"

The user DBTest does not exist in my newly restored database but does exist in the publi role in that database. I am not allowed to delete this user from the public role (option greyed out). Help !!!

Thanks,

TimH