I had to migrate a SQL Server 2008 instance on to new hardware.
I choose to detach the databases and attach the databases on the new SQL Server 2008 instance on the new hardware. This was easy, but the origin SQL Server instance used mixed SQL Server and Windows Authentication Mode. This also meant that I had to migrate SQL Server logins, as the logins where a mix of SQL Server logins and Active Directory domain accounts/groups.
Both the SQL Server logins and domain accounts/groups has an unique SID (Security Identifier), which ties the logins in the SQL Server with the users in the database.
While the domain accounts/groups resides in the Active Directory domain controllers, the SQL Server logins only resides in the SQL Server. This means, moving domain accounts/groups is easy, by just creating the same users in the new SQL Server instance, but SQL Server logins is not. I could either choose to:
- Recreate the SQL Server logins with new users in each of the databases, as new SQL Server logins will get new SIDs and therefore not be tied to the old database users. This also requires that you know all the passwords – alternative reconfigure all the client applications using SQL Server logins 🙁
- Transfer the SQL Server logins with SID and password 🙂
How to transfer SQL Server logins? See the KB article: How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008. Easy 🙂