Transfer SQL 7 to SQL2000

[STibbs]

I am having problem with getting the user logins from a restored SQL 7 database to work on my SQL2000 server.

I created the database on the 2000 box then restored the files from the SQL 7 to the SQL 2000 box. The logins I created first then restore the database but then I could not connect to the table using ODBC. My next try, I restored the database then tried to create the logins but they will not connect properly to the database. I can use ODBC to connect but they will not connect with the tables.

Does anyone have any ideas? I have not had this problem before. If it a SQL 2000 issue?

 

[Corran007]

Have you copied or recrated all the users you had on the old server?

IF not, this should be done first.

Then you have a couple options. Your users are out of sycn from the new servers users and the old databases users. You can either delete them and readd them, or you can run this.

DECLARE @USERNAME VARCHAR(40)

DECLARE User_Cursor CURSOR FOR
select name from sysusers WHERE issqluser = 1 and suser_sname(sid) is null and name not in ('guest','dbo','public')

OPEN User_Cursor

FETCH NEXT FROM User_Cursor
INTO @USERNAME

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC sp_change_users_login 'Auto_Fix',@USERNAME,NULL
Print @USERNAME + 'Changed'
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM User_Cursor
INTO @USERNAME
END

CLOSE User_Cursor
DEALLOCATE User_Cursor
GO

That script will resync the users if they are from a different server as long as the names are the same. I use it all the time when i copy my prod databases to my test machine.

Hopefully this will help your problem.

 

[STibbs]

Thanks that worked great. I will add this to my list of fix it items.

Yes I had copied the old id from the old server, I just could not get the ids to connect.

Much appreciated.

 

[MaffewW]

When possible I try and not use the autofix option, though if there are a lot of orphaned users or like yourself if you're copying databases to a test environment it is sometimes the best option, you may have read this

http://support.microsoft.com/defaul...port/kb/articles/q298/7/58.asp&NoWebContent=1

Matt

Brighton, UK