2

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.

Improve this question
3
  • I'm not clear on what is missing. Is it a database user (database level) or a login (instance level)?
    – Ed Leighton-Dick
    Aug 28, 2009 at 17:58
  • this may indeed be a mirroring bug! check out support.microsoft.com/kb/978791
    – Nick Kavadias
    Feb 5, 2010 at 15:33
  • Nick, I disabled automatic failover and all is well.
    – Matt Rogish
    Feb 5, 2010 at 17:01

5 Answers 5

Reset to default
1
+100

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.

Improve this answer
5
  • I buy this for the login. But, why would a TRIGGER be missing? What if the trigger was created by the user with the missing sid, would it drop those owned objects?
    – Matt Rogish
    Dec 2, 2009 at 15:58
  • can't explain the missing trigger. But i can tell you with confidence that sql server will not just drop triggers to spite you. It's not a bug! something that you're not aware is going on. Can you provide more information? What did the trigger do?
    – Nick Kavadias
    Dec 2, 2009 at 16:06
  • OH! Trigger is inserting from mirrored database to non-mirrored database on PRIMARY. I don't even think the db exists on the failover.... I wonder if tha twould cause problems?
    – Matt Rogish
    Dec 2, 2009 at 16:10
  • problems yes. delete the trigger, no. Some other process may be going on here that you're not aware of
    – Nick Kavadias
    Dec 2, 2009 at 16:26
  • I can't imagine any process would drop a trigger -- we have usual log shipping replication, db mirroring, and backup jobs. Not much other maintenance
    – Matt Rogish
    Dec 2, 2009 at 20:51
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.

Improve this answer
3
  • The mirror server never had the login, but why would that be a problem on the primary
    – Matt Rogish
    Sep 3, 2009 at 17:31
  • It shouldn't be. The SQL Server wouldn't automatically drop the login. Why isn't the login on the mirror? If the logins don't exist on both servers then applications or users can't login when the database is on that server.
    – mrdenny
    Sep 4, 2009 at 0:46
  • Oversight. It has since been added, of course
    – Matt Rogish
    Sep 10, 2009 at 15:41
1

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.

Improve this answer
0

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

Improve this answer
-1

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .