locked
replicate sql 2005 logins between servers RRS feed

  • Question

  • I have two sql 2005 servers. One is the live server, the other is a backup. Is there a way of getting a live replication of the sql logins between these two server?

    Ideally, I would like both the servers to be identical. I am doing this through replication, but if there is a better method, please let me know.
    Thursday, April 16, 2009 1:13 PM

All replies

  • Hi Jagdip,

    This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005 on different servers.

    http://support.microsoft.com/kb/918992

    Regards,

    Tarek Ghazali
    SQL Server MVP
    Web Site : http://www.sqlmvp.com
    Sunday, April 19, 2009 9:57 AM
  • Hi Tarek,

    thank you for this. I had already found this article and was looking at ways of using it.

    So far, it looks like I will have to create the following triggers to get everything I want being replicated to the second server:

    - a server trigger for when a login is created/altered/removed
    - a database trigger for each database for when a user is created/altered/removed
    - a database trigger for each database for when a role is created/altered/removed
    - a database trigger for each database for when security is changed (e.g. allowing/revoking select/update/delete)

    This is a lot of work, and I was hoping for a better method.
    Sunday, April 19, 2009 10:15 AM