Database Users mapped to SQL Logins get orphaned if there is a mismatch in SID on the mirror server

    General discussion

  • Symptoms
    If you have an application that connects to a Mirrored Database using SQL Authentication and are using the Failover Partner connection attribute of the SQL Native Client, then you might get the following errors:

    ERROR #1:
    Microsoft SQL Native Client error '80040e09'
    The EXECUTE permission was denied on the object

    ERROR #2:
    OLE DB provider "SQLNCLI" for linked server "<server name>" returned message
    "Login timeout expired".
    Msg 18456, Level 14, State 1, Line 1
    Login failed for user '<login name>'.
    Msg 4060, Level 11, State 1, Line 1
    Cannot open database "<database name>" requested by the login. The login failed.

    On examining the database user properties for this SQL Authenticated Login, you will find that this login is orphaned.

    Also, if you execute
    EXEC sp_change_users_login 'Report';
    against the database from a Management Studio query window, you will find that it reports that the database user is not mapped to any login on the SQL instance. If you fix the issue using the AUTO_FIX or UPDATE_ONE parameter of the stored procedure, then on failover again, you will run into this issue.

    This happens because the SQL Login on the mirror server gets orphaned due a mismatch in SIDs for the login on the principal and mirror servers. SQL Login SIDs are generated by the server on which the login was created. This happens to be different for each login as this is a unique identifier. When the principal database fails over, it tries to identify the database user using the SID of the login on the principal. Once, it finds that the SID is different, the connection fails as a valid user mapping to the database cannot be made for the SQL Authenticated login used to connect to the database.

    With the help of the sp_help_revlogin script given in the KB Article below, you can script out the principal server SQL Login and then use the script generated to create the same login on the mirror server. After that you can grant it the necessary rights on the user database. The difference between a CREATE LOGIN T-SQL command and the script generated by this login is that the SID also gets extracted which can be used to create the login on the mirror server to ensure uniformity of SIDs.
    918992 How to transfer the logins and the passwords between instances of SQL Server 2005;EN-US;918992

    Repro Steps
    1. Create a SQL Login on the server and map to a database
    2. Create the same SQL Login on the mirror server
    3. Mirror the database using SSMS Mirroring Wizard
    4. Failover the database
    5. Execute a stored procedure or a T-SQL command on the mirrored database under the context of the SQL login created above and you would see login failed/connectivity errors mentioned in the Symptoms section.

    More Information
    Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to the SQL Server server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.

    Before setting up Database Mirroring, you should ensure that the logins mapped to the Principal database are created on the Mirror Server too. This problem doesn't occur while using Domain Accounts because Domain Account have the same SID throughout the domain where as the SIDs generated SQL Authenticated Logins are system specific and vary from server to server.

    Database Mirroring Whitepaper

    Useful Links from SQL Server 2005 Books Online
    Setting Up Login Accounts for Database Mirroring
    Managing Logins and Jobs After Role Switching
    sp_change_users_login (Transact-SQL)
    Troubleshooting Orphaned Users

    Microsoft Online Community Support
    Wednesday, March 04, 2009 10:46 AM