Pages

Thursday, March 18, 2010

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 problem 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.

2 comments:

  1. Hi Dan,
    I hadn't sp_help_revlogin procedure.
    After some research I found out that is should be created by DBA.
    Here some relevant Microsoft links
    How to transfer logins and passwords between instances of SQL Server
    How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

    ReplyDelete
  2. Thanks,
    You are absolutely right.
    I forgot to mention it in the post.

    Thanks for reading.
    Dan

    ReplyDelete