Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL migration Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 7:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
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
Post #1427366
Posted Wednesday, March 6, 2013 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1427370
Posted Wednesday, March 6, 2013 7:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
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)
Post #1427382
Posted Wednesday, March 6, 2013 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1427404
Posted Wednesday, March 6, 2013 9:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:37 AM
Points: 5,879, Visits: 13,009
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 )

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

Post #1427478
Posted Thursday, March 7, 2013 7:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
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 :-(
Post #1427979
Posted Thursday, March 7, 2013 7:47 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:37 AM
Points: 5,879, Visits: 13,009
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.


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

Post #1427983
Posted Thursday, March 7, 2013 7:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 2,013, Visits: 3,447
sp_help_revlogin as mentioned above does the passwords correctly.

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

Edit: Beat me to it George
Post #1427986
Posted Thursday, March 7, 2013 7:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
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?
Post #1427996
Posted Thursday, March 7, 2013 8:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:37 AM
Points: 5,879, Visits: 13,009
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


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

Post #1428004
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse