How to add user and grant access to secondary database server on log shipping?

How to add user and grant access to secondary database server on log shipping?

If database is configured in logshipping the secondary server is in STANBY recovery mode. Because of this secondary server database was in read-only mode where you cannot add user to secondary database.

Workaround

1. Create Login in the primary server
USE [master]
GO
CREATE LOGIN [Kalyan_RO] WITH PASSWORD=N'kalyan_ro123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [KalyanDB]
GO
CREATE USER [Kalyan_RO] FOR LOGIN [Kalyan_RO]
GO
USE [KalyanDB]
GO
EXEC sp_addrolemember N'db_datareader', N'Kalyan_RO'
GO
With the above step you created SQL login in Primary server and provided read access on KalyanDB.

2. Create the login with same name in Secondary Server  (Here you need to remember one thing, even though you a login is created with same name in secondary server you wont get same SID, becoz SIDs will be different in primary and secondary servers)

For that reason you need to get the SID from primary server and create the same login in secondary server with same SID.

Before creating login in secondary server, use the below query to get the SID of the login
select name, sid from master..syslogins where name='Kalyan_RO'
Now use the below syntax to create the login in secondary server with same SID

CREATE LOGIN [Kalyan_RO] WITH PASSWORD= N'kalyan_ro123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, SID=0x8389C417AA312E499C03DC4AE914899E

Using the above script login will be created and user rights will be mapped to it after log backup was restored on secondary server. User level mappings will come thru log backup as that is database level.