January 8, 2018 at 8:02 pm
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).
January 9, 2018 at 12:49 am
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
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
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
January 9, 2018 at 11:09 pm
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