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 ««12

Transfer of Logins from 2k to 2K8 Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 7:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1560421
Posted Thursday, April 10, 2014 7:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:09 AM
Points: 166, Visits: 861
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.
Post #1560427
Posted Thursday, April 10, 2014 7:40 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 5,871, Visits: 12,971
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.


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

Post #1560434
Posted Thursday, April 10, 2014 1:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 6,414, Visits: 13,796
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"
Post #1560604
Posted Monday, April 14, 2014 7:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:09 AM
Points: 166, Visits: 861
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!!
Post #1561483
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse