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. :-)
asked Apr 29, 2010 at 10:01 AM in Default
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.
answered Apr 29, 2010 at 10:05 AM
Grant Fritchey ♦♦
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).
answered Apr 29, 2010 at 11:36 AM