November 22, 2010 at 5:23 pm
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?
November 22, 2010 at 5:25 pm
November 22, 2010 at 6:24 pm
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
November 23, 2010 at 3:40 am
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
November 23, 2010 at 4:47 am
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.
November 23, 2010 at 5:01 am
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. 😀
November 23, 2010 at 5:27 am
Glad to know that it worked !
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 23, 2010 at 6:06 am
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! 😀
November 24, 2010 at 8:04 am
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]
November 24, 2010 at 8:44 am
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.
November 24, 2010 at 12:18 pm
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" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply