Grant access on Secondary but not on primary in SQL Server logshipping [duplicate]

Question

I have a strange situation wherein users need permission on log shipped DB on secondary server but not on primary. The problem is, the login has access on many other DBs on primary server apart from log shipped DB and that needs to be maintained. My environment is SQL Server 2012 EE.

Is it possible to achieve this?

As a workaround what I have tried is granting temporary access on my primary DB just before the backup log-shipping starts and once the backup completes I have added a step to remove it. But the issue with this workaround is, the users can still access the primary log shipped DB when I try to grant them access just before the log backup. Any help would greatly be appreciated.

Answer 1

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';