I have an occasional problem when moving a database from one server to another that although the users on the database are transferred correctly, the logins on the server are not. This is fair enough, as the logins are associated with the server, and the users are associated with the database. The problem is that there is no obvious way to re-create the logins correctly as if you try to do it, either the login won’t map to the old user, or it will complain that it can’t create a new user because it already exists.
One solution is to simply delete the old users and re-create, but this can be a real pain in the neck, especially if you have complex rights or can’t delete them because of the way things are configured. You can get around this with renaming users and re-creating and then switching things over and deleting the old ones, but this is all very tedious.
Fortunately, there is a stored procedure that will deal with the problem. First of all, create the new login without mapping it to a user. Once you have done that, open a query window in the appropriate database and run
sp_change_users_login ‘auto_fix’, ‘username‘
where username is the username that you want to fix. Â It takes a split second to run, so you can do a whole load of them without too much hassle.