0

I have some legacy SQL Server 2000 databases to migrate to SQL Server 2012.

In my dry run, I have done this:

I created empty databases of the same names in SQL Server 2008.
Then I backed up the SQL Server 2000 databases and restored them, overwriting the 2008 databases.
Then I created empty databases of the same names in SQL Server 2012.
Then I backed up the 2008 databases and restored them, overwriting the 2012 databases.

At this point, I can log in to the SQL Server 2012 databases from within Query Analyzer, as Domain Admin using Windows Authentication, and query the databases; and SPs invoked from within QA work as they should.

The SQL Server 2012 server has been set up to support Windows and SQL authentication, but the SQL Authentication-mode connection strings from client applications do not work yet. During the restore process, the users have been orphaned, as expected.

It has been a few years since I've had to migrate databases between server versions, so I cannot remember how to recreate the SIDs for these orphaned users, though there used to be a fairly quick way to do that, IIRC. There was a script, and it didn't require me to do any preparation on the "from" server; it was simply run on the "target" server.

Please jog my memory.

Improve this question
1
  • I hope after enabling mixed mode authentication, for allowing SQL Server logins to connect, you restarted SQL Server instance. Again you did not tell us what error you face when login gets failed
    – Shanky
    Jan 21, 2015 at 19:59

1 Answer 1

Reset to default
1

You can use the following to list orphaned sql users:

USE foo
go

EXEC sp_change_users_login 'Report';

and 

USE foo
go

EXEC sp_change_users_login 'update_one', 'foouser', 'foouser';

to fix each one.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.