Post reply

Restrict the users to take database offline and detach. send a mail notification also if anyone tried to do so.

  • January 9, 2018 at 12:49 am

    #1975025

    You first need to look at restricting the user permissions so they are unable to take a database offline. No users should be able to do this.

    Thanks

  • January 9, 2018 at 1:07 am

    #1975029

    I know, Limited permission is the appropriate solution of this.
     but in my case, I cant remove permissions due to some business reasons, that's why I have to implement these workarounds.

  • January 9, 2018 at 1:28 am

    #1975031

    Your workarounds are unfortunately useless if you can't restrict permissions. Someone who wants to drop a DB can disable the trigger, drop the DB, enable the trigger and there's no record. You'll catch accidents, not malicious activity.
    Detach doesn't fire DDL triggers afaik.

    Speak to the owners of those 'business reasons' and explain that they're putting the entire server at risk, see if  they're willing to change the 'reasons' or sign off that it's their fault if someone exploits the holes.

    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

  • January 9, 2018 at 11:09 pm

    #1975220

    I Agree, No workaround can prevent these actions if it is intentionally, But definitely help us track the accident and prevent the any accidental changes. Not sure how can i track the detch database.

Viewing 4 posts - 1 through 5 (of 5 total)

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