Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transfer of Logins from 2k to 2K8


Transfer of Logins from 2k to 2K8

Author
Message
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
Perry Whittle (4/10/2014)
Jake Shelton (4/10/2014)
My question is whether I need to move the logins to a 2k5 box as an interim step before relocating again to 2008.

No you don't and if youre not concerned with server roles my script is perfect

One thing I like about Perry's script is exclusion of '##%' and 'SA'. You might face some issue if you execute this script for 'SA' password will change to from where you have copied the login. And what if you forget to change?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Jake Shelton
Jake Shelton
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 916
Perry Whittle (4/10/2014)
Jake Shelton (4/10/2014)
My question is whether I need to move the logins to a 2k5 box as an interim step before relocating again to 2008.

No you don't and if youre not concerned with server roles my script is perfect


It may well be, but I possibly neglected to mention that I need the passwords to be ported across too...if memory serves, without the 2K5 step, the hashing will fail as it has changed base algorithm between 2000 and 2005.
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
Jake, the sp_help_revlogin will bring passwords across. straight to 2008. I have done it myself many times.

Perry, master.sys.sql_logins is not available in SQL2000 so Jake will not be able to reverse engineer his login details with your script.

---------------------------------------------------------------------
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
Jake Shelton (4/10/2014)
if memory serves, without the 2K5 step, the hashing will fail as it has changed base algorithm between 2000 and 2005.

Step not required. The algorithm between 2008 and 2012 has changed.



george sibbald (4/10/2014)
Jake, the sp_help_revlogin will bring passwords across. straight to 2008. I have done it myself many times.

Perry, master.sys.sql_logins is not available in SQL2000 so Jake will not be able to reverse engineer his login details with your script.

My bad, this is the correct script for SQL2000


SELECT 'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
dbo.fn_varbintohexstr(password) +
' HASHED, SID = ' + dbo.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(DB_NAME(dbid)) +
', DEFAULT_LANGUAGE = ' + language +
'; ALTER LOGIN [' + name + '] DISABLE;'


FROM master.dbo.sysxlogins
WHERE name <> 'sa' AND password IS NOT NULL



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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Jake Shelton
Jake Shelton
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 916
george sibbald (4/10/2014)
Jake, the sp_help_revlogin will bring passwords across. straight to 2008. I have done it myself many times.

Perry, master.sys.sql_logins is not available in SQL2000 so Jake will not be able to reverse engineer his login details with your script.


Worked great, thanks!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search