Home / server / Questions / 60056
Accepted
Matt Rogish
Asked: 2009-08-29 07:53:36 +0800 CST

SQL Server 2005 dropping objects from DB on recovery?

  • 772

SQL Server 2005 dropped a user from one of our mirrored databases after we recovered from a simple Windows-update induced restart of the machine.

I checked and the login is not in the mirror server (it is in the primary). If the system experienced a failover, would that explain why the user was removed from the user DB upon recovery back to the primary? I can't find anything in the doc that would indicate that SQL server would DROP orphaned users (http://msdn.microsoft.com/en-us/library/ms175475%28SQL.90%29.aspx). Is there anything I should look for in the logs?

Edit: Setup before failure: PRIMARY - Login OK. User in database OK. MIRROR - Login does not exist. User does not exist in database.

After failure: PRIMARY - Login OK. User does not exist in database. MIRROR - Login does not exist. User does not exist in database.

EDIT: After the recovery of a DB system after WINDOWS UPDATES reboot last week, we noticed a trigger was missing from one of our tables, as was another user. I am 100% confident this is NOT our doing (we only have two sa's and I am one of them). WHAT THE HECK IS GOING ON? This has to be a bug with SQL Server 2005 mirroring.

sql-server-2005 database-mirroring

5 Answers

  1. Did the mirror server ever have the login created? If not you'll need to script out the login from the primary system so that the SIDs match.

    • 1

  2. The user is likely in the mirror database, just not sync'd to a login. If the user was there when you setup mirroring, even if the login did not exist, SQL Server would not drop it. it's there.

    If you added it later, it would move across the mirror. Not the login, but the CREATE USER would be transferred.

    • 1
  3. Best Answer

    Here's what i think happened, this is based on the hunch in your question tag of orphaned-user, so i may be wrong.

    1. your database failed over from the principal to the mirror.
    2. the database server on the mirror (now the principal) did not have the login scripted out from the primary, but instead it was created as a new login. This would mean the sid's didn't match , so there were problems with the user logging in after the fail-over.
    3. To fix the login problem sp_change_users_login 'Update_One' was run to fix the login issue. This would have changed the user sid in the database to match the sid of the login on the database server.
    4. The database is failed over again. Now, the login sid on the database server no longer matches. What to do? use sp_change_users_login again to fix the problem.

    What should have happened:
    the logins from the principal that are used in the database which participates in mirroring are scripted out (as mr denny suggested) to the mirror. Easiest way to do this is to use sp_help_rev_login You can also use the SSIS transfer logins task.

    The sid for database server logins is visible in sys.server_principals. The sid for the database users is in sys.database_principals. Check these to ensure there isn't a miss-match.

    • 1

  4. It was our 3rd party vendor's app dropping the trigger as part of their app. Insane.

    • 0

  5. The trigger is misisng because somebody drop it. Period. No offense intended, but if you are one of the administrators and you don't know where server principals reside and why database principals can be orphaned after a failover, then I cannot trust that you know why a trigger dissapears.

    Use the default administrative trace to find out who dropped the trigger and when. If you're lucky, the trace file was not yet recycled. Set in place audit to keep track of the changes occuring on your server from now on. For administrators it shouldn't be a 'surprise' when a trigger dissapears. And no, you can't blame the product.

    • -1