Sql-server – migrating legacy databases from SQL Server 2000 via SQL Server 2008 to SQL Server 2012: users and logins

migrationsql-server-2000sql-server-2008sql-server-2012

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.

Best Answer

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.

Related Question