|
First off, thank you to everyone in this community for all of your help!!! I am in the planning stage of moving from one MS SQL Server to a replacement MS SQL Server. I am transferring ALL of the user databases from the old SQL Server and moving them to the new SQL Server that will have the same computer name, instance name, IP address, and logical drive layout. I am using backup/restore. This is how I plan to move the logins: http://support.microsoft.com/kb/918992/ I am going to be transferring login's and passwords, but am unsure of when I should import the logins. I was hoping to restore one user database and check that that database was working correctly before moving over every user database. To make this happen, I planned to move the logins over before any user databases were loaded. My concern is that while importing the logins and passwords it won't apply the security to the user databases, because they don't exist yet on that server. Hopefully I am making sense here and you can help me. :-)
(comments are locked)
|
|
Run the import on the logins first. Then when you restore the databases, you might hit problems, but you can fix them quickly by running sp_change_users_login. Here's an article on orphaned users. Assuming you get all the system id's correct on the logins though, you're unlikely to even hit this issue. +1 - I agree, I ran the import logins process a few weeks ago when I started moving my first database over. I have moved 5 or 6 more now and no issues yet.
Apr 29 '10 at 10:10 AM
Fatherjack ♦♦
Great link! I always get excited when I come across a MS article that isn't 10 pages long!
Apr 29 '10 at 10:18 AM
Ron 3
(comments are locked)
|
|
I've just done this successfully myself and I disagree with Grant (gulp!). The script you linked to does work nicely although it doesn't carry over the login's default language so I added that and posted the revised version here: http://sqlblogcasts.com/blogs/davidwimbush/archive/2010/04/09/copying-logins-to-another-server.aspx. If you run the resulting script without any databases it will fail on logins that have a default database that isn't there but you can restore a database that contains users that link to logins that aren't there yet. A user and its permissions are in the user database, while the login is stored in a system database. There is a foreign key type relationship but it is not enforced because it's cross-database. So I say create the databases first and then add the logins. You can, of course, just do one database and run in the login(s) needed for just that database (as long as none them has a different user database as its default database).
Apr 29 '10 at 11:39 AM
Grant Fritchey ♦♦
You're right, it's chicken and egg. I'm a bit fixated on doing it this way round because I got burned by orphaned users when I set up a log shipping standby server without getting the login SIDs the same. It was all working perfectly until I found all the non-Windows logins were broken. I had to start all over again because sp_change_users_login doesn't work too well on a standby/read-only database!
Apr 29 '10 at 11:49 AM
David Wimbush
(comments are locked)
|
