How to create/add a new database User to Log Shipping database

Consider the situation when you need to add a new database
User and SQL Server Login to Log shipped database.

The main duduk perkara here is that the Login in the Log Shipping
SQL Server must have same SID as Login in primary
SQL Server.

Here is a simple way to add new SQL Server Login with
same SID as in source server:

1. Create a new login on the primary SQL Server and map
    it to required DB.

2. Execute this command in the primary SQL Server
    to generate a script for creating the SQL Server Login
   with same SID:   Exec sp_help_revlogin 'LoginName'
   Copy the script and execute it in Log Shipping SQL
   Server.

3. After the restoring the transaction logs on the Log Shipping
    machine, the new added database user should be mapped
   to the new created SQL Server Login and you should be able
   to connect/login to the Log Shipping Server with the new Login.

Tweet