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

more ▼

asked Apr 29, 2010 at 10:01 AM in Default

Ron 3 gravatar image

Ron 3
97 5 5 6

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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

more ▼

answered Apr 29, 2010 at 11:36 AM

David Wimbush gravatar image

David Wimbush
5k 29 31 34

+ 1. Disagree away. I'm wrong so often I almost don't notice anymore. I think the situation comes down to a chicken or egg moment though, as you ably describe. You're going to have issues on one side or the other.
Apr 29, 2010 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, 2010 at 11:49 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 29, 2010 at 10:05 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+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, 2010 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, 2010 at 10:18 AM Ron 3
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 29, 2010 at 10:01 AM

Seen: 2145 times

Last Updated: Apr 29, 2010 at 10:01 AM