Grant access on Secondary but not on primary in SQL Server logshipping
On Primary



Create a new Login [testLogin] and a new User [testUser] with the requiered rights and roles:

USE [master]
GO
CREATE LOGIN [testLogin] WITH PASSWORD=N'test123'
    , DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE [Test]
GO
CREATE USER [testUser] FOR LOGIN [testLogin];
GO
USE [Test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testUser];
GO



These login and user must not exist and be different from your current login and user.


Once [testUser] is available on Secondary, remove [testLogin] from Primary:

USE [master]
GO
DROP LOGIN [testLogin];




  Other option on Primary



Remove the orignal Login


You must plan your update if you have to remove login for a short time and make sure you disconnect them first.


Create a new Login with the same name

CREATE LOGIN...

Link Orphan users to the newly created Login for all DBs except the log shipped DB:

USE [DB1]
GO
sp_change_users_login @Action='update_one'
    , @UserNamePattern='testUser'
    , @LoginName='testLogin';




  On Secondary [testLogin]:



Create a new login [testLogin]:

USE [master]
GO
CREATE LOGIN [testLogin] WITH PASSWORD=N'test123'
     , DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

Link the orphan user [testUser] (shipped from Primary) to the new login [testLogin]:

USE [Test]
GO
sp_change_users_login @Action='update_one'
    , @UserNamePattern='testUser'
    , @LoginName='testLogin';