Logins from SQL 2000 to SQL 2008

Question

Post reply

Logins from SQL 2000 to SQL 2008

Patti Johnson

SSCertifiable

Points: 7054
More actions
November 22, 2010 at 5:23 pm

#229543

Hey all,
I have a sql 2000 database and I'm taking it from an old server and restoring it to a new SQL server 2008 r2 installation on another server.
Is there a way to get the security\logins defined on the old server and import them to the new SQL 2008 security\logins without having to redefine them individually?

Viewing 11 posts - 1 through 10 (of 10 total)

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

JeremyE SSCoach Points: 16200 More actions · Answer 1

MS provides a couple stored procedures to do just that:

http://support.microsoft.com/kb/246133/[/url]

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 2

Hey Jeremy,

I read that article once before. My question/problem is this....the Drives of the old server where the sql 2000 master and user databases are located is being disconnected from the old server and reconnected to the new sql 2008 server (they don't have money to get more drives). Therefore, I have no way to have both the old and new databases to transfer from. I was trying to use the bcp feature to export syslogins.dat from 2000 master database and run sp_resolve_logins on 2008 master database using the syslogins.dat. I ran it and it runs successfully on the 2008 server but no new logins are added. So, I'm not sure what to do. I tried using the bcp in to read in the syslogins.dat into the 2008 master database but it gives me an error - adhoc updates to system catalogs not allowed so I stopped and wrote a message to you all.

Patti

Adiga One Orange Chip Points: 27284 More actions · Answer 3

Hi Patti,

I hope you have used the script in "Method 2". Execute that script on the SQL Server 2000 instance and run the output in SQL Server 2008 instance. It had worked for me.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 4

Hey, Is it safe to assume that method 2 will for work going from 2000 to 2008? It looks like it will work going from 2000 to 2005 but nothing for 2000 to 2008.

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 5

Hey there. Thanks I ran method 2. Thank God I was doing a simulation test. We set up a scenario that would mimic that actual cutover. I ran method 2 and took output and ran on sql 2008 server. It worked. Thanks very much. 😀

Adiga One Orange Chip Points: 27284 More actions · Answer 6

Glad to know that it worked !

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 7

Thanks again. I'm turning it over to the users for testing now so we will see what happens. Let's keep our fingers crossed! 😀

shaun.stuart SSCertifiable Points: 7067 More actions · Answer 8

Interesting. For me, using sp_help_revlogins did not work going from 2000 to 2008.. But then, I was actually going from 2008 to 2008 R2. Still, it didn't work for me and I had to go from 2000 to 2005 and then from 2005 to 2008 R2. I assumed this was because the way SQL encrypts the passwords internally changed between 2005 and 2008. I wrote about my experiences at http://shaunjstuart.com/archive/2010/08/transferring-logins-from-sql-2000-to-2008/[/url]

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 9

Thanks for that note. I read your post and also your experience. I'm going to read up on the 2005 to 2008 procedure.

I'm waiting to find out if the accounts work. I see them but I'm not sure if they work. I could connect via odbc.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

shaun.stuart (11/24/2010)
Interesting. For me, using sp_help_revlogins did not work going from 2000 to 2008.. But then, I was actually going from 2008 to 2008 R2. Still, it didn't work for me and I had to go from 2000 to 2005 and then from 2005 to 2008 R2. I assumed this was because the way SQL encrypts the passwords internally changed between 2005 and 2008. I wrote about my experiences at http://shaunjstuart.com/archive/2010/08/transferring-logins-from-sql-2000-to-2008/[/url]

To transfer logins between instances of SQL Server 2005 and SQL Server 2008 use the following supported article from Microsoft

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

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

"Ya can't make an omelette without breaking just a few eggs" 😉