locked
How to move Logins/Users during mirroring Fail over from Principal to Mirrored Servers in Mirroring RRS feed

  • Question

  • Hi,

    I have 3 servers

    1. is for Principal Server

    2. Is for Mirrored Server

    3. Is for Witness Server.

    Assuming , that failore happens , how do we ensure that the logins/Users from principal server to Mirrored server is pushed with the help of Witness Server.

    Is there any seperate check for this process, or does it do the job manually ?


    hemadri

    Tuesday, December 4, 2012 11:02 AM

Answers

  • Database users gets created automatically in the mirror database when you create it on principal server, but as logins are created in master database, you need to create it on the mirror server as well when you are creating the login in principal server. Later when there is a failover, you need to map the users to login

    Tuesday, December 4, 2012 11:13 AM
  • Hi Hemadribabu,

    basically you have to make sure that logins will be created on both systems. To do it on the mirror is a manual job!

    You will only run into problems with SQL Logins because of their unique SId.
    We have implemented sp_help_revlogin on each principal / mirror where SQL Logins are needed.
    When a new SQL Login will be created we run the sp_help_revlogin procedure and execute the generated sql statement on the mirror.

    Example (Principal):

    CREATE LOGIN [NewSQLLogin] WITH PASSWORD = 'asdfasdfsd', CHECK_POLICY = OFF CHECK_EXPIRATION = OFF;
    
    -- now we run the sp_help_revlogin procedure
    EXEC sp_help_revlogin 'NewSQLLogin'
    
    -- Copy the output of the proc on the mirror instance

    Basically we run into the following problems when using SQL Logins:

    - Different passwords if password change is forced (e.g. MUST_CHANGE or CHECK_POLICY)
    - SQL Logins can not be created manually on both machines. After the login has been created a login with same SId has to be created on the mirror.

    If possible try to avoid SQL Logins!


    Uwe Ricken

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

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    • Proposed as answer by Sudeepta Ganguly Thursday, December 6, 2012 7:41 AM
    • Marked as answer by Maggie Luo Sunday, December 23, 2012 9:50 PM
    Thursday, December 6, 2012 6:49 AM
  • Adding to Uwe, you can also download the system stored procedure sp_help_revlogin from this location: http://support.microsoft.com/kb/918992/

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Thursday, December 6, 2012 7:41 AM

All replies

  • Database users gets created automatically in the mirror database when you create it on principal server, but as logins are created in master database, you need to create it on the mirror server as well when you are creating the login in principal server. Later when there is a failover, you need to map the users to login

    Tuesday, December 4, 2012 11:13 AM
  • Hi there,

    Couple of thing you need to take care here:

    1. Users from primary after restored to secondary become orphaned.
    2. Logins mapped at primary becomes users at both primary and secondary. But if this login do not exists at the secondary become orphaned.

    So to avoid access issues after setting the mirror run the below script and find out any orphaned users exists. Make automatic failover and run the script will give you an idea what is happening with the users.

    select u.name from master..syslogins l right join

        sysusers u on l.sid = u.sid

        where l.sid is null and issqlrole <> 1 and isapprole <> 1  

        and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' 

        and u.name <> 'system_function_schema')

        and u.name <> 'sys'

     thanks

    kumar

    Wednesday, December 5, 2012 5:43 AM
  • We create logins/jobs and etc on the Mirroring server in advance m why waiting for failover?

    http://www.mssqltips.com/sqlservertip/1166/login-failures-connecting-to-new-principal-after-sql-server-database-mirroring-failover/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, December 5, 2012 6:07 AM
  • Uri,

    There might be a scenario a new login is created after setting up mirroring . 

    -----------------------------

    Thanks,Suhas Vallala

    Wednesday, December 5, 2012 7:40 AM
  • You are right

    --SQL Logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
    + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
    + sys.fn_varbintohexstr(sid) + ', '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 
    'us_english'))
    + ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 
    'OFF' END
    + ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
    FROM sys.sql_logins
    WHERE name<>'sa'
    UNION ALL
    --Windows logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 
    'us_english'))
    FROM sys.server_principals
    WHERE type IN ('U','G')
    AND name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM');


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, December 5, 2012 7:47 AM
  • Hi Hemadribabu,

    basically you have to make sure that logins will be created on both systems. To do it on the mirror is a manual job!

    You will only run into problems with SQL Logins because of their unique SId.
    We have implemented sp_help_revlogin on each principal / mirror where SQL Logins are needed.
    When a new SQL Login will be created we run the sp_help_revlogin procedure and execute the generated sql statement on the mirror.

    Example (Principal):

    CREATE LOGIN [NewSQLLogin] WITH PASSWORD = 'asdfasdfsd', CHECK_POLICY = OFF CHECK_EXPIRATION = OFF;
    
    -- now we run the sp_help_revlogin procedure
    EXEC sp_help_revlogin 'NewSQLLogin'
    
    -- Copy the output of the proc on the mirror instance

    Basically we run into the following problems when using SQL Logins:

    - Different passwords if password change is forced (e.g. MUST_CHANGE or CHECK_POLICY)
    - SQL Logins can not be created manually on both machines. After the login has been created a login with same SId has to be created on the mirror.

    If possible try to avoid SQL Logins!


    Uwe Ricken

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

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    • Proposed as answer by Sudeepta Ganguly Thursday, December 6, 2012 7:41 AM
    • Marked as answer by Maggie Luo Sunday, December 23, 2012 9:50 PM
    Thursday, December 6, 2012 6:49 AM
  • Adding to Uwe, you can also download the system stored procedure sp_help_revlogin from this location: http://support.microsoft.com/kb/918992/

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Thursday, December 6, 2012 7:41 AM