Ultimate Login Restore Solution?
-
Hi all,
We are moving database from Server A to B this weekend. I always seem to get hassle with logins when restoring a db.
Can anyone give me the ultimate solution for restoring a database (with users and roles) and then getting the logins - NT AND SQL and WITH passwords onto the new server and tied to the users/roles?
Cheers!
-
You have to move the logins first and then restore your user databases. To move logins you have to create 2 sps: sp_hexadecimal and sp_help_revlogin in master db; please follow the relevant steps here http://support.microsoft.com/kb/246133 Even the MS article seems to refer to sql2005, it works for sql 2000 as well. The idea is that the logins and users to have the same sid (if you're not familiar with sid concept please read in BOL).
-
Most of my headaches have been between 6.5 and 2000 I must admit. This seems more straight forward!
Cheers
(However, I am running my script on the new server and it has taken 30 minutes so far...)
-
SQL crazies! Stopped the script, re ran it - 10 seconds! The 30 minutes was a sham!
-
Once you move your databases over run the following:
sp_change_users_logins 'report'
That will give you a 'report' of logins that are not properly setup. Either they don't exist or they are orphaned.
If they don't exist - add them.
If they do exist run this:
sp_change_users_login 'auto_fix', ''
Change the to the actual login and that will 'unorphan' the login.
-SQLBill
-
Hmmm any clue on getting aliases across?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply