Every couple of months I get to migrate a database backup to some other server and most often the users need to be recreated. As a reminder for myself, I’ve written this post.
The detailed explantaion can be found in this KB article on technet
Step to resolve orphaned users
Use SSMS and connect to the server.
Open a new query window
To detect orphaned users in a database:
Now I know the SQL user account, I can create the login with the necessary credentials in SSMS under Security – Logins. I only create the account and grant it server role ‘public’, no specific user mapping, give a temporary password, till the responsible can give me the correct one, the default db is master.
sp_change_users_login 'update_one', 'someuser', 'someuser'go
Now the user properties can be modified to set default database to this database name, alter password and grant other roles to the user on the database.