Fixing Orphaned Users after Attaching a Database

29
Jan
2007

Fixing Orphaned Users after Attaching a Database

The following MS link - http://support.microsoft.com/kb/274188/ - will help you to troubleshoot and fix orphaned logins in a database, re-joining the database username to the SQL Server Login.

However if you've moved a database from one machine (A) to another (B) using the attach database method and B doesn't have the logins available to be fixed in the first place - then you need to create the required SQL Server logins first. MS have a great helper script that will extract the Login name, SID, default database and password as well as create a script that can be run on the new machine B to re-create all the required logs (and SIDs). You won't need to fixed any orphaned users since the user information in the newly attached DB and SQL Servers login information will now match.

http://support.microsoft.com/kb/246133/- How to transfer logins and passwords between instances of SQL Server.

A huge time saver and makes moving a DB using the attach method a breeze.

Category: