migrating logins SQL2000 - SQL2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

migrating logins SQL2000 - SQL2008

Author

Topic

ganda.lf
Starting Member

1 Post

Posted - 2009-06-25 : 03:22:10

Goodmorning,
How can i migrate the logins i have on sql 2000 to a new istance of SQL 2008 with all password?
Tankyou for your cooperation
Paolo

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2009-06-26 : 07:37:23

backup and restore

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2009-06-26 : 10:13:13

If you do not know the logins, you can try to add them with a SQL script. Note, if they have updated their hash algorithm, this may not work for SQL 2000 -> SQL 2008

For each login, find the SID, and hashed password from SQL 2000


select name, sid, convert(varbinary(255), password)
from syslogins

From this, create CREATE LOGIN statements for each login using the HASHED keyword for the password

 CREATE LOGIN testlogin with password = 0x12382387 HASHED, SID = 0x02835256

http://msdn.microsoft.com/en-us/library/ms189751.aspx

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-26 : 10:15:40

use an ssis package from 2008 to copy the logins from the sql 2000 database over to the sql 2008 database, just working the alternative way around but i'm sure it should work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources