Wednesday, 13 May 2009

Autofix DB users

NOTE! The resolution in this post is being deprecated, please use this post for an updated approach.

When you are copying databases across servers, the SQL users will lose their mappings.
Instead of deleting and recreating, you can use the autofix login script. This will try to match up the user on the database with one that is already available on the server.
EXEC sp_change_users_login 'Auto_Fix', 'username', NULL, 'password';
If the user doesn't exist, then the script will create a new user with the supplied password.