SQL migration

Question

SQL migration

balasach82

SSCrazy Eights

Points: 9653
More actions
March 6, 2013 at 7:01 am

#271961

Whats the best way to copy logins, users, logins with password along with exact status of enable/disable from source 2008 to destination server 2008

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

balasach82 (3/6/2013)
Whats the best way to copy logins, users, logins with password along with exact status of enable/disable from source 2008 to destination server 2008

Microsoft provides a downloadable query named sp_help_revlogin: there's multiple versions so download the version that matches your server(ie SQL2008)

How to transfer logins and passwords between instances of SQL Server

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 2

what about using ssis to move logins

or

using management studio to script out logins and users (but it comes with default disable login option)

Lowell SSC Guru Points: 323504 More actions · Answer 3

balasach82 (3/6/2013)
what about using ssis to move logins
or
using management studio to script out logins and users (but it comes with default disable login option)

SSIS has a Transfer logins Task as well, so that is another option; I personally do most of my work in TSQL, and not much in SSIS.

Transfer Logins Task

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

george sibbald SSC Guru Points: 104210 More actions · Answer 4

be aware the SSIS transfer logins task changes the password for sql logins to an arbitrary value (for security reasons but also makes it useless 🙂 )

---------------------------------------------------------------------

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 5

absolutely right. SSIS just creates a copy of the login, but the password is not copied. whats the purpose then to just copy login only. I treid scripting out logins, whch had some jumbled password. Even that too did not work. It just created login in SLQ 2008. Is there any difference in login passwords in SQL 2005 and 2008.

I have to do this first from 2005 to 2008. Am wound up in the first stge 🙁

george sibbald SSC Guru Points: 104210 More actions · Answer 6

if you use sp_help_revlogin it copies out the password in an encrypted format. Just run the script as its produced on your destination server and the login will be created with the same sid and password as on the source server, and it will all marry up.

The password is only an issue with SQL logins, windows authenticated logins will not have an issue as the OS level password is trusted.

no difference between 2005 and 2008.

---------------------------------------------------------------------

Gazareth One Orange Chip Points: 27737 More actions · Answer 7

sp_help_revlogin as mentioned above does the passwords correctly.

http://support.microsoft.com/kb/918992

Edit: Beat me to it George 🙂

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 8

Georege, gazareth, correct. any below options are futile. I was under the impression, that scriptingo out logins which also has password, would work. But that is not the case.

scripting out logins

transfer logins task in ssis

Another question, once moving the logins using the revlogin, I can script out USERS from management studio. correct?

george sibbald SSC Guru Points: 104210 More actions · Answer 9

balasach82 (3/7/2013)
Georege, gazareth, correct. any below options are futile. I was under the impression, that scriptingo out logins which also has password, would work. But that is not the case.
scripting out logins
transfer logins task in ssis
Another question, once moving the logins using the revlogin, I can script out USERS from management studio. correct?

passwords will be correct with sp_help_revlogin

users will go across with the database

---------------------------------------------------------------------

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 10

Okie, users come with database. But they would be orphaned is it not?. Then how to use change users login procedure to fix all orphaned users? Do you have any query handy

Gazareth One Orange Chip Points: 27737 More actions · Answer 11

That's why the help_rev_login script includes the SID - should prevent users being orphaned.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 12

balasach82

SSCrazy Eights

Points: 9653

March 7, 2013 at 8:32 am

#1594492

Thanks Gazareth, thanks all.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 13

The roles would also be copied/set after running the script in destination? since there wont be orphaned users, the login would hav access as it was in source server, eg: db_datareader in DB1, db_ddladmin in DB2 etc

Or should we assign the users/login to roles again through script or through manually?

george sibbald SSC Guru Points: 104210 More actions · Answer 14

database permissions for users are all held within the database, so once the login is tied up with the user via the sid, user database permissions will all be correct.

---------------------------------------------------------------------