December 1, 2004 at 11:18 am
Good Day,
I backed up a DB from one SQL server complete. I did a restore of that DB just backed up to another server. The SQL user does not exist on the new server. I tried running the SQL script option from the Enterprise Mgmt console on the old DB to give me a script to recreate the login and DB owner properties on the new server. This all seemed to work okay. However, when I go into the DB properties on the new server, I do not see the newly created user in the user folder on the new DB. Also if I look at the permissions on the new DB, I do not see this user in there either. If I try to run the sp_changedbowner procedure, it tells me that the sql user is already the owner. If I look at the user login in the security folder and dblClick on it, it tells me that the SQL login does not exit in the sysusers table. But I can go in and see the SQL user login in that table.
Is there a simple way to backup a DB from one server and restore it to another server and have it preserve the SQL login users and roles they are assigned.
I would appreciate any help.
December 1, 2004 at 11:38 am
Create all necessary logins that are in your original database in new server, restored the database and run stored procedure "sp_change_users_login" to map the users in restored database to the logins you created in new server.
December 1, 2004 at 3:52 pm
Great Info!
Worked like a charm!
Thanks again for all the help!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply