Fixing SQL User accounts after a database restore

(I can’t take credit for this post, it was written by an ex-college of mine, but I find it very useful, so I posted it here).

The following script maps an existing database user to a SQL Server login, it can be very useful after restoring a database.

exec sp_change_users_login ‘auto_fix’, ”

Post restore this saves having to drop the database’s user account, add the account back and reassign permissions to required stored procedures, view etc.

If no results are returned, then as a double check run the

exec sp_change_users_login @Action=’Report’

if you do get a result then run

exec sp_change_users_login @Action=’update_one’, @UserNamePattern=, @LoginName=;

This should fix up any syncronisation problems with the account.