How to fix all orphaned SQL Logins

If you do lots of database restores from one server to another you may often come across server logins becoming "orphaned" from their associated database users. There is an easy fix for this by using sp_change_users_login but what if there are multiple logins that have become orphaned?

The cursor below should fix them all for you:

-----------------------------------------

-- Auto fix all orphaned user accounts

-----------------------------------------

DECLARE @SQL VARCHAR(MAX) 

SET @SQL =

       'DECLARE @userVar VARCHAR(30)

              DECLARE users CURSOR

              FOR

              SELECT NAME

              FROM sys.database_principals

              WHERE type = ''s''

                     AND NAME NOT IN (

                           ''guest''

                           ,''sys''

                           ,''INFORMATION_SCHEMA''

                           ,''dbo''

                           )     

              OPEN users   

              FETCH NEXT

              FROM users

              INTO @userVar 

              WHILE @@FETCH_STATUS = 0

              BEGIN

                     EXEC sp_change_users_login ''auto_fix''

                           ,@userVar

                     FETCH NEXT

                     FROM users

                     INTO @userVar

              END   

              CLOSE users

              DEALLOCATE users'

EXEC(@SQL)

[This piece of code is ideal for a Snippet]