locked
Sysadmin permissions lost after mirroring failover. RRS feed

  • Question

  • SQL2012 .  After databases failover to Secondary ( Database Mirroring ), Primary Server was restarted .

    While failback to Primary , observed that DBA group lost sysadmin permissions. NO errors logged anywhere.

    Please suggest.

    Wednesday, June 17, 2020 5:44 PM

All replies

  • Hi mallikha,

    Database mirroring failover may cause data loss, but I don't heard that it will cause the DBA group to lose sysadmin permissions.

    Try to view the default Trace to  find the security changes in your database. Please refer to this article.

    If you cannot get any information through default trace, re-grant the DBA group sysadmin permissions, then create DDL triggers or SQL Server Audit to track security changes, Wait until the problem occurs next time, and then analyze it.Please refer to this article.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 18, 2020 3:42 AM
  • Hi mallikha,,

    Is there any update on this case? Was your issue resolved?

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 22, 2020 1:01 AM
  • System database permissions do not get mirrored. Once you add the permissions on each server, you should be all set going forward

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 1, 2020 7:02 AM