Troubleshooting orphaned users

Reminder

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:

Use <MyDatabase>

go

sp_change_users_login 'report'

go

Query result

UserName    UserSID
someuser    0xCEC65E3AE7C0B64CA05435BE2B580EE9

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.

Use <MyDatabase>

go

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.