question

Ron 3 avatar image
Ron 3 asked

When should I transfrer the logins and passwords?

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. :-)

sql-server-2005loginmigration
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ron 3 avatar image Ron 3 commented ·
Great link! I always get excited when I come across a MS article that isn't 10 pages long!
2 Likes 2 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
+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.
1 Like 1 ·
David Wimbush avatar image
David Wimbush answered

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).

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.