Sql-server – Login issue in Logshipping

1. Log in to the serverA using "mydomain\myuser" credentials.

2. Run below TSQL :

   EXEC master.dbo.sp_addlinkedserver 
                               @server = N'serverB', 
                               @srvproduct=N'', 
                               @provider=N'SQLNCLI', 
                               @datasrc=N'serverB', 
                               @catalog=N'MyDatabase',
                               @provstr='Integrated Security=SSPI;'
   GO
   EXEC sp_addlinkedsrvlogin 'serverB', true  
   go
   

3. Check if the "serverB" exists in sys.servers :

   select * from sys.servers
   

4. Check if the "serverB" works from ServerA

   select * from serverB.master.dbo.sysdatabases
   

Below steps should work :

1. Create windows login on ServerA
2. Create user in database mapped to login
3. Drop login on ServerA - (OPTIONAL -if you want to have that login intact then leave it, else drop it).
4. Grant any required permissions to user in database

5. Create login on log shipping ServerB

   • a. If using SQL authentication then create login while specifying SID from ServerA

   • b. If using Windows authentication, create login without specifying SID

      ---To query for the SID on the ServerA:
     
       Select SID
       From sys.database_principals
       Where name = 'Test'
     
      ---To create the login on the ServerB :
     
       Create Login [Test]
       With SID = '<put SID from query here>',
       Password = '<add password here>'
     

6. Make sure log shipping jobs have completed one full run since completion of step 4