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.

8 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
  3. Enthusiastic words written in this blog helped me to enhance my skills as well as helped me to know how I can help myself on my own. I am really glad to come at this platform.
    Veeam Availability Suite for VMware

    ReplyDelete

  4. Great post! looks so Good informative post i love to read. Thanks you so much.

    Microsoft Server 2016 Migration
    Microsoft Server 2016 Support

    ReplyDelete

  5. Great, Your post is just outstanding! thanks for such a post,its really going great work.

    Microsoft Server 2016 Repair
    Microsoft Server 2016 Migration

    ReplyDelete

  6. It's really awesome post. Keep update and Your blog is really useful for me.


    Austin website design services

    austin web design

    ReplyDelete

  7. This post is absolutely fantastic and Lots of great information. Thanks

    Server 2016
    Microsoft Server 2016 Migration

    ReplyDelete