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

  • Guys,
    I need to restrict all db users to take OFFLINE and DETACH the database from the SQL server and send a notification mail If anyone wants to try to do so. 
    1. I created the ddl trigger for ALTER_DATABASE. But when I take the database offline, the trigger is fired and error also is shown but database goes OFFLINE also. Please help me to achieve this.
    2. If I detach a database- DDL trigger for Alter_Database is not fired.(Please suggest a way so that I can trigger a mail if anyone detaches the database).

  • 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

  • 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.

  • 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
  • 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 5 posts - 1 through 4 (of 4 total)

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