Post reply

How to find who changed the database to Single_user mode.

  • July 4, 2013 at 3:55 am

    #279485

    Hi All,

    One of our databases changed to single_user mode. But we could not find the exact user, hostname etc. Is there is any way to find these details?

    I had gone through the SQL Server Error logs and Windows event error logs. But could find the exact user who changed the database to single_user mode? Do database automatically changed to single user mode?

    Regards,

    Varun

  • July 4, 2013 at 4:04 am

    #1629790

    SQL will never automatically change the recovery model. If it was changed, someone changed it.

    Check the default trace, if the change was recent enough it may still be in there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 5, 2013 at 8:17 am

    #1630087

    If the security's tight (which is rarely the case, from experience taking on new DB), in SQL you can track who has the ALTER DATABASE permission.

    But most of the times almost everyone has full sysadmin access (unfortunately) which makes things complicated.

    What you could do however is setup a DDL trigger and log any information you can for the next time it occurs.

    Otherwise go as what Gail's suggest.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply