Database restore on destination and fix orphan user

In the article, we are going to demo a backup and restore process from source DB server to destination DB Server. In the demo we are performing below high-level tasks...

1. Create a login and grant access to a DB on our source server.

 2. Take a DB backup on the source server.

3. Restore the DB backup on destination server.

4. Login creation with same SID on destination server.

1. Create a login and grant access to a DB on our source server.

USE [master]

GO

CREATE LOGIN [Login_Name] WITH PASSWORD=N'****', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

use [master];

GO

USE [education]

GO

CREATE USER [Login_Name] FOR LOGIN [Login_Name]

GO

USE [DB_Name]

GO

ALTER ROLE [db_datareader] ADD MEMBER [Login_Name]

GO

USE [DB_Name]

GO

ALTER ROLE [db_datawriter] ADD MEMBER [Login_Name]

GO

Or

Go to connect option click on it and login with user name, go to new query -> Default Instance-> security-> user name ‘Abhishek -> user mapping with database-> give permission for read\write

 2. Take a DB backup on the source server and move it on destination.

Below is the syntax to take DB full backup... 

backup database logic to disk='R:\backup\logic.bak';

Note: In this artical, we are using Logic DB for demo.

3. Restore the DB backup on destination server.

     First, we need to find out DB files in the back and location and second step, restore the DB with help of move commend.

4. Login creation with same SID on destination server.

Now, first we need to get orphan user list. 

--Syntax: 

use logic

exec sp_change_users_login @action='Report';

Once we get the user name and SID we can create a login with the SID...

create login [login_name] with password='*****',SID=0X1121212121212122342344556Df55