Orphaned Logins

When you transfer SQL Server logins to a new server with DTS or create them on the new server manually, the SIDs don’t match up with the server that you are importing your database backups from. To avoid this, Microsoft has created handy scripts (for transferring from 2000 to 2005, for transferring between 2005 instances). If you’ve already botched it up, you can find the orphaned logins like so:

EXEC sp_change_users_login ‘Report’

And you can fix them one at a time like so:

EXEC sp_change_users_login ‘Update_One’, ‘username’, ‘login’

which links users in databases with the username “username” to SQL Server logins with the name “login”.

Comments are closed.