Move logins from SQL 2K to SQL 2005

Question

Post reply

Move logins from SQL 2K to SQL 2005

Alan RAczek

SSC Enthusiast

Points: 146
More actions
December 20, 2006 at 1:35 pm

#114978

This HAS to have been asked before. I tried using sp_help_revlogin in article 246133 but it did not work.
Obviously there needs to be a script that runs on SQL 2000 that will make a sp which will generate output that can be used by SQL 2005. I have tried a couple different scripts but get nowhere.

Please help!

Viewing 7 posts - 1 through 6 (of 6 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

How to transfer logins and passwords between instances of SQL Server

http://support.microsoft.com/default.aspx/kb/246133

MohammedU
Microsoft SQL Server MVP

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

i learned too late that the SSIS job doesn't transfer passwords. Had to manually assign the password on like 20 SQL Logins on Friday.

MohammedU SSCoach Points: 19644 More actions · Answer 3

I am using the sP_help_revlogin since long time without any issues...

How to transfer logins and passwords between instances of SQL Server

http://support.microsoft.com/default.aspx/kb/246133

MohammedU
Microsoft SQL Server MVP

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

I use this script.

SET NOCOUNT ON

SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''

,', @defdb = ''' + dbname + ''''

,', @deflanguage = ''' + language + ''''

,', @encryptopt = ''skip_encryption'''

,', @passwd ='

, cast(password AS varbinary(256))

,', @sid ='

, sid

FROM syslogins

WHERE name NOT IN ('sa')

AND isntname = 0

Indianrock SSC-Insane Points: 20333 More actions · Answer 5

I used the scripts here, worked great.

http://blogs.interfacett.com/jeff-jones-blog/2006/11/9/moving-logins-across-multiple-sql-servers.html

beccyk SSC Veteran Points: 232 More actions · Answer 6

I used sp_help_revlogin when I transferred our servers to 2005 from 2000. The thing that caught me is that some of the SQL logins didn't work after the transfer since the passwords become case sensitive in 2005 where they weren't in 2000. So some applications were using passwords of the wrong case and they had be updated.