Why are users not able to login to restored DB after the backup is restored to another SQL server?

Question

I have restored a sql database in another server, but existing users are not able to access the newly restored database. Please advise.

Answer 1

@LawrenceT-MSFT The above scenario happens as the user in the source DB is not an login in the new SQL server where the database is being imported.

To fix this issue, you need to create an login on the new Server with SID of the user present in the source server.
Here are detailed steps you need to perform -

1. After importing the DB on the new SQL server, please execute the below command.
   select sid from sys.sysusers where name = <username>
   <username> is the existing user in the database which you want to connect to the new server.
2. Please take the SID of the user and create a login with below script on the target server.
   CREATE Login <username> WITH password = <Password>, SID = <SID received from previous step>

The login will be created with the same SID name and now you can use above created login to connect to database.

Regards
Navtej S

Answer 2

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

See if this guide helps - https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-ver15#detect-orphaned-users

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav