For several years now I’ve been moving development databases between SQL Servers using backup and restore. When you restore the database on the target server, the logins for the database are invariably broken with the database user having an empty login name, meaning that they cannot log in to the database. My usual fix is to delete the database user and re-add it. Paul Hayman however pointed out a useful stored procedure to fix broken logins:
sp_change_users_login 'Auto_Fix', 'username'where username is the name of the account to fix.
The Auto_Fix option will attempt to match the broken login with an existing user with the same name.
More information on this can be found in the MSDN documentation. Specific things to note are that it only works with SQL Server and not Windows logins, and that you must be a member of the sysadmin fixed server role for it to work.