locked
Transferring Logins SSIS task and DB mirroring RRS feed

  • Question

  •  

    Hi guys,

     

    I want to create an automatic job on SQL 2005 to transfer logins between 2 servers, (Principle and Mirror). If I use the transfer logins task I get the error:

     

    "The database "x" cannot be opened. It is acting as a mirror database."

     

    I guess this is because it is trying to assign the default databases for the logins??

     

    I've also looked at [sp_help_revlogin] procs but that requires manual intervention.

     

    How does everyone else transfer logins in this situation?

     

    Thanks in advance..

     

     

    Monday, May 12, 2008 2:37 PM

Answers

  • Disco,

     

    Be careful with issuing create login commands manually, because if they are SQL Logins, they won't have identical SID's, and you will have mismapped database users at failover that you will have to clean up by matching the database user back to the correct SQL Login with sp_change_users_login.  Microsoft has a set of stored procedures that can script the CREATE LOGIN commands so that it maintains the correct SID, and you could easily modify the procedure to test if the login exists before issuing the CREATE Statement to the Mirror.  This is how I have kept my logins in sync between my Principle and Mirror while maintaining the SID.  The links to the Microsoft code for this are in the below article.

     

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins&referringTitle=Home

     

    You can edit the [sp_help_revlogin] procedure to not set the default database, and to check for existence really easily.  I can send you a copy of my edited procedure if you would like, with the caveat that it is provided as is, and you should validate it prior to using it.

     

    Tuesday, May 13, 2008 12:31 PM

All replies

  • Hi,

     

    While connecting to the mirror server you can make use of -d parameter to specify the db to connect or change the default database to master db in SSMS GUI and then connect to the server.

     

    Even if you use sp_helprevlogin procedure, it will map to the default database while creating the login.

     

    Monday, May 12, 2008 2:47 PM
  • Hi Vidhya,

     

    Thanks for replying but I'm not sure I follow.

     

    I thought the Transfer Logins task would execute the CREATE LOGIN on the master db. I think that I get this error because it is referencing mirrored db's as the default db for the login in the create statement.

     

    Thanks

     

     

     

    Monday, May 12, 2008 2:55 PM
  • Welcome to MSDN forums,

    You can refer this link, which describes the transfer of logins using SSIS, http://blogs.msdn.com/psssql/archive/2007/06/22/how-to-configure-database-mirroring-for-use-with-odbc-applications.aspx

    You can also create the login in the mirror server to use the same SID as the principal using the steps shown in the below link,
    http://sql-articles.com/index.php?page=articles/failover_db_mirroring.html

    - Deepak
    Monday, May 12, 2008 3:44 PM
  • Thanks Deepak but the SSIS transfer logins task will only work if I change the default db of each of the logins to a database that is not mirrored which I don't want to have to do that.

     

    I resolved the issue by creating a linked server of the principal server on the mirrored server. I then wrote a script on the mirror to query the new logins in the principal sys.syslogins that don't exist on the mirror and then use that to create the login statements dynamically (without the defaultdb clause) and execute these statements on the Mirror.

     

    Tuesday, May 13, 2008 11:11 AM
  • Disco,

     

    Be careful with issuing create login commands manually, because if they are SQL Logins, they won't have identical SID's, and you will have mismapped database users at failover that you will have to clean up by matching the database user back to the correct SQL Login with sp_change_users_login.  Microsoft has a set of stored procedures that can script the CREATE LOGIN commands so that it maintains the correct SID, and you could easily modify the procedure to test if the login exists before issuing the CREATE Statement to the Mirror.  This is how I have kept my logins in sync between my Principle and Mirror while maintaining the SID.  The links to the Microsoft code for this are in the below article.

     

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins&referringTitle=Home

     

    You can edit the [sp_help_revlogin] procedure to not set the default database, and to check for existence really easily.  I can send you a copy of my edited procedure if you would like, with the caveat that it is provided as is, and you should validate it prior to using it.

     

    Tuesday, May 13, 2008 12:31 PM
  •  

    Hey Jonathon,

     

    I should have mentioned that I created my script from sp_help_revlogin so i maintain the SID's. I execute the LOGINPROPERTY to get the passwords using OPENQUERY to execute it remotely on the principal and then execute the dynamic CREATE LOGIN statements created to create the logins on the mirror. So when the sh*t hits the fan I only have to run the ALTER DATABASE command to kick off the failover.

     

    Cheers

     

     

     

     

     

     

    Tuesday, May 13, 2008 12:51 PM
  • Hi Disco,

    Can you discuss in a bit of detail on how did you accomplish the task please?
    Tuesday, November 10, 2009 6:45 AM
  • Can you pls send me an edited copy of your sp_help_revlogin stored procedure please?
    Thursday, November 12, 2009 11:05 AM
  • Thursday, November 12, 2009 4:31 PM
    Answerer