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.

Matt Rogish
  • 1,512
  • 6
  • 25
  • 41

5 Answers5

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • The mirror server never had the login, but why would that be a problem on the primary – Matt Rogish Sep 03 '09 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 04 '09 at 00:46
  • Oversight. It has since been added, of course – Matt Rogish Sep 10 '09 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.

Steve Jones
  • 795
  • 5
  • 8
1

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.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • 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 02 '09 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 02 '09 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 02 '09 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 02 '09 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 02 '09 at 20:51
0

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

Matt Rogish
  • 1,512
  • 6
  • 25
  • 41
-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.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22