locked
Recreating users on mirror database RRS feed

  • Question

  • I setup mirror database. I haven't done anything explicit about user logins that our applications use to connect to the database - just backed up primary DB, restored on mirror server with NORECOVERY and set up mirroring.

    Now user accounts have to be created on mirror and given access to required database. However, it is not possible to map user to the database serving as a mirror.

    So it seems the only way is to fail over, make mirror primary and proceed with creating users. Is this right? Is there any other way to create users for the mirror without failing over? The problem in our environment is that fail over is somewhat of a hassle and a lot of things have to be done on several computers.
    Thursday, August 14, 2008 10:42 PM

Answers

  •  

    Use the Trasfer Login Task with the SID option enabled.

     

    http://blogs.msdn.com/psssql/archive/2007/06/22/how-to-configure-database-mirroring-for-use-with-odbc-applications.aspx

     

    Here are the details:

    1)    Create a login on the primary server (for eg: ssistest with a password of Password1) and set the default database to master.

    2)       Open SQL Server BI Studio (Business intelligence studio under SQL server program group) and create a SSIS package as per the following steps:

    a)      Add two SMOServer Connection managers (one for the primary and one for the secondary).

    b)      Add the Transfer Logins Task from the Toolbox and double click on it to get the properties screen.

    c) Click on the Logins List and choose the ssistest user that we had setup for the mirroring.

    d) In the properties of the Transfer Logins Task, under ‘Misc’ section ensure that we have 'CopySids’ set to True (This is very important)

    e)      Now execute the task. You should see that the package executed successfully and transferred the logins.

    3)      Now on the secondary server you should see the ssistest login. This will be in disabled state. You need to enable the same and set the password to the same one as on the primary. (Transfer logins task does this by design and the login on the destination server is assigned a random password)

    4)      Now on the primary server change the default database of ssistest to mirrordb and make him a dbo in that database.

    5)      Now failover the database to secondary.

    6)      Change the default database of ssistest on the secondary to mirrordb (we can see that he will be automatically mapped to dbo role).

    7)      Failover the mirrordb back to primary.

    8)      Now create a ODBC DSN mapped the usual way

    9)      Test the application It should work without a problem.(My app has the connection string the following way: dsn=ssistest;uid=ssistest;pwd=*********Wink

    10)      Failover the mirrodb and test your connection again from your application. It will still work.

     

    Thursday, August 14, 2008 11:47 PM
  • Inaddition to what Ross said you can try creating login with sid before configuring mirroring and then try
    http://www.mssqltips.com/tip.asp?tip=1166

    - Deepak
    Friday, August 15, 2008 4:06 AM

All replies

  •  

    Use the Trasfer Login Task with the SID option enabled.

     

    http://blogs.msdn.com/psssql/archive/2007/06/22/how-to-configure-database-mirroring-for-use-with-odbc-applications.aspx

     

    Here are the details:

    1)    Create a login on the primary server (for eg: ssistest with a password of Password1) and set the default database to master.

    2)       Open SQL Server BI Studio (Business intelligence studio under SQL server program group) and create a SSIS package as per the following steps:

    a)      Add two SMOServer Connection managers (one for the primary and one for the secondary).

    b)      Add the Transfer Logins Task from the Toolbox and double click on it to get the properties screen.

    c) Click on the Logins List and choose the ssistest user that we had setup for the mirroring.

    d) In the properties of the Transfer Logins Task, under ‘Misc’ section ensure that we have 'CopySids’ set to True (This is very important)

    e)      Now execute the task. You should see that the package executed successfully and transferred the logins.

    3)      Now on the secondary server you should see the ssistest login. This will be in disabled state. You need to enable the same and set the password to the same one as on the primary. (Transfer logins task does this by design and the login on the destination server is assigned a random password)

    4)      Now on the primary server change the default database of ssistest to mirrordb and make him a dbo in that database.

    5)      Now failover the database to secondary.

    6)      Change the default database of ssistest on the secondary to mirrordb (we can see that he will be automatically mapped to dbo role).

    7)      Failover the mirrordb back to primary.

    8)      Now create a ODBC DSN mapped the usual way

    9)      Test the application It should work without a problem.(My app has the connection string the following way: dsn=ssistest;uid=ssistest;pwd=*********Wink

    10)      Failover the mirrodb and test your connection again from your application. It will still work.

     

    Thursday, August 14, 2008 11:47 PM
  • Inaddition to what Ross said you can try creating login with sid before configuring mirroring and then try
    http://www.mssqltips.com/tip.asp?tip=1166

    - Deepak
    Friday, August 15, 2008 4:06 AM