mssql user permission and mapping gone after DB restore with REPLACE option

Question

I dont know why is there no question or reference to this but it seems there is no way to restore user mapping and permission to databases after restoration of db with replace option. i get it user lost their permission because of the SID but why is there no easy way to fix this? sp_change_users_login is not working.

Answer 1

When you restore a database, you restore a database, and if there was a database in place with the same name, this database is entirely flattened. There is no merge of logins or anything. BACKUP and RESTORE are low-level operations that works with pages with a minimal understanding of what is in those pages.

If you are regularly restoring a database from one environment to another, and you want to retain permissions, logins created in the target environment, you need to cater for this by having a post-install script to permissions and users.

When it comes to SID for SQL logins, they can be handled without post-processing, though. When you create a login you can say:

CREATE LOGIN nisse WITH PASSWORD = 'ToppSikrit' SID = 0x.....

So once you have created a login on one server, you capture the SID and the use this SID when you create a login with the same on the other servers.

Answer 2

Hi @public cloud ,

Welcome to Microsoft Q&A!

A database user can become orphaned after a database is restored or attached to a different instance of SQL Server where the login was never created. Please refer to Troubleshoot orphaned users (SQL Server) to get more information. And this blog provide a way to solve this issue, see below:

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.