Grant Windows logins to read a secondary database of log shipping?

Question

The following link, Creating Users on secondary server in log shipping, shows how to create a database login on the secondary server. How to grant Windows logins on the secondary server to access the database? The primary server and secondary server are not in the same domain so I cannot create the Windows domain user on the Primary server.

Answer 1

Since the secondary in a log-shipping configuration is either READ ONLY or RESTORING, you cannot make changes to the database (i.e. create a USER) until a ROLE CHANGE happens (i.e. when you failover from primary to secondary). You can create a LOGIN on the secondary server.

You can use this script - How to transfer logins and passwords between instances of SQL Server to script logins - both SQL and Windows.

If you are using SQL Server 2014, then you can just create a ROLE and then grant that role connect any database and select all user securables. This works on readonly databases in logshipping configuration as well.

Note that above is not that elegant if you keep security in mind, but still is an alternative that can be considered.