question

Lawrence080-MSFT avatar image
0 Votes"
Lawrence080-MSFT asked SaurabhSharma-msft edited

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

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

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered SaurabhSharma-msft edited

@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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
1 Vote"
VaibhavChaudhari answered

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://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.