After MSSQL database restore you might to meet with situation that in DB will be orphan users. Simply, it is empty login name without any assignment with user. We can check it and then will see that there is 2 the same login names but with different SID numbers like on the picture below.
To list all problematic users you have to execute below command using a user with “sysadmin” permissions.
|
1 |
EXEC sp_change_users_login 'Report' |
Here we have a problem with user called reports. First we have to ensure that user with that name is exist. If yes, then execute below command.
|
1 |
EXEC sp_change_users_login 'Auto_Fix', 'reports' |
The result will be:
|
1 2 3 |
The row for user 'reports' will be fixed by updating its login link to a login already in existence. The number of orphaned users fixed by updating users was 1. The number of orphaned users fixed by adding new logins and then updating users was 0. |
In case there is no such user we need to create it, for example by using this command.
|
1 |
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password' |