locked
how we add user to database which is in restoring state RRS feed

  • Question

  • Hi i want add user to DB which is restoring state, because it is getting mirrored from different instance?

    Thanks,

    Maddy

    Tuesday, May 8, 2012 2:22 PM

Answers

  • Hallo Maddy,

    you have add the login first to the Principal Server (you've mentioned a mirroring system!)
    If it is a Windows Login it is quite simple and you can follow the following steps:

    On the principal system:

    USE Master
    GO
    IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'domain\user')
     CREATE LOGIN [domain\user] FROM windows
    USE [yourdatabase]
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'domain\user')
       CREATE USER [domain\user] FROM LOGIN [domain\user]
    -- add the user to roles ...
    EXEC sp_addrolemember ...

    On the mirror system you only have to run the first step (creation of the login

    Due to design mirroring will transfer all DDL / DML from the prinicpal to the mirror automatically and that means in fact that user and their settings will be distributed to the mirror partner, too.

    If you have a SQL login it is - a little mor - complicated

    Due to the fact that a SQL login will use a SQL Server generated SId instead of the unique SId from AD you have to have a workaround which can result in two different solutions:

    1. Scipt the login from the prinicpal server with sp_help_revlogin and create the login on the mirror
    http://support.microsoft.com/kb/918992/en-us

    2. If you don't want to process it the above way you have to take care about the linkage between the login and the user when the mirror becomes the new principal in the case of a failover. Than you have to execute sp_change_users_login on the database.

    http://msdn.microsoft.com/en-us/library/ms174378(v=sql.105).aspx

    I would strongly recommend to use the first solution because than you will not have to take care about the correction of orphaned users when the failover will happen. BTW: The same process has to be undertaken when you fall back :)


    Uwe Ricken

    MCM SQL Server 2008
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Marked as answer by Maggie Luo Tuesday, May 15, 2012 8:14 AM
    Wednesday, May 9, 2012 8:40 AM

All replies

  • you cannot add the user or login while database in restoring state, it should be online.

    Incase if you have configured Logshipping or Mirroing --In the initial stage only you need to sync with their similar SID of the Production to DR.

    other wise you have to break mannually then use the Production SID the apply on the DR or what ever you have requirements.


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 8, 2012 2:26 PM
  • But Udaya, this db is involved in logshipping and db i want to add use is secondary db
    Tuesday, May 8, 2012 2:32 PM
  • Please check

    http://sql-channel.com/2011/10/31/how-to-create-a-user-on-a-secondary-server-database-for-logshipping-in-standby-recovery-mode/

    http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/13/orphaned-users-with-database-mirroring-and-log-shipping.aspx

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Edited by Rama Udaya Tuesday, May 8, 2012 2:46 PM
    Tuesday, May 8, 2012 2:35 PM
  • do you need to create a SQL login using SQL Authentication or can you use a SQL login using Windows Authentication?

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 8, 2012 2:44 PM
  • Hallo Maddy,

    you have add the login first to the Principal Server (you've mentioned a mirroring system!)
    If it is a Windows Login it is quite simple and you can follow the following steps:

    On the principal system:

    USE Master
    GO
    IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'domain\user')
     CREATE LOGIN [domain\user] FROM windows
    USE [yourdatabase]
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'domain\user')
       CREATE USER [domain\user] FROM LOGIN [domain\user]
    -- add the user to roles ...
    EXEC sp_addrolemember ...

    On the mirror system you only have to run the first step (creation of the login

    Due to design mirroring will transfer all DDL / DML from the prinicpal to the mirror automatically and that means in fact that user and their settings will be distributed to the mirror partner, too.

    If you have a SQL login it is - a little mor - complicated

    Due to the fact that a SQL login will use a SQL Server generated SId instead of the unique SId from AD you have to have a workaround which can result in two different solutions:

    1. Scipt the login from the prinicpal server with sp_help_revlogin and create the login on the mirror
    http://support.microsoft.com/kb/918992/en-us

    2. If you don't want to process it the above way you have to take care about the linkage between the login and the user when the mirror becomes the new principal in the case of a failover. Than you have to execute sp_change_users_login on the database.

    http://msdn.microsoft.com/en-us/library/ms174378(v=sql.105).aspx

    I would strongly recommend to use the first solution because than you will not have to take care about the correction of orphaned users when the failover will happen. BTW: The same process has to be undertaken when you fall back :)


    Uwe Ricken

    MCM SQL Server 2008
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Marked as answer by Maggie Luo Tuesday, May 15, 2012 8:14 AM
    Wednesday, May 9, 2012 8:40 AM
  • You can't add the users while the database is log shipped. You have to do it when you switch over and bring the database "online" (perform recovery), to get it out of the restoring or standby state.

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, May 9, 2012 9:45 AM
  • If a database in mirroring, you add user to the principal Database it will be shipped to the mirrored database as well. I I believe you were looking for how to automatically move the new logins from principal instance to the mirrored instance.

    There is an automated solution for this checked out this 2 articles:

    http://www.msbicoe.com/post/2012/04/05/Automatically-Transfer-and-Synchronize-SQL-Server-logins-SIDs-Passwords-and-Permissions.aspx

    or

    http://www.msbicoe.com/post/2012/03/18/A-simple-Way-to-Migrate-SQL-Logins-from-One-Instance-to-Another.aspx


    Wednesday, May 9, 2012 9:46 PM