触发器防止删库

5天前
  1. CREATE TRIGGER [Trig_Prevent_DB_Drop] ON ALL SERVER
  2. FOR DROP_DATABASE AS
  3. BEGIN
  4. SET NOCOUNT ON
  5. IF SUSER_NAME() NOT IN ('sa')--可自己修改
  6. BEGIN
  7. DECLARE @xmlEventData XML, @Message VARCHAR(MAX);
  8. SET @xmlEventData = eventdata()
  9. SELECT @Message = CHAR(10) + CHAR(13) + REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ')
  10. + CHAR(10) + CHAR(13) + CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)'))
  11. + CHAR(10) + CHAR(13) + CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)'))
  12. + CHAR(10) + CHAR(13) + CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)'))
  13. + CHAR(10) + CHAR(13) + CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)'))
  14. + CHAR(10) + CHAR(13) + CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)'))
  15. + CHAR(10) + CHAR(13) + CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)'))
  16. + CHAR(10) + CHAR(13) + CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
  17. + CHAR(10) + CHAR(13) + 'Database cannot be dropped from the Server. Please contact DBA!'
  18. + CHAR(10) + CHAR(13);
  19. RAISERROR (
  20. @Message
  21. ,16
  22. ,1
  23. ) WITH LOG;
  24. ROLLBACK;
  25. END
  26. END
  27. GO
  28. ENABLE TRIGGER [Trig_Prevent_DB_Drop] ON ALL SERVER
  29. GO

0
1
0

添加评论

正在回复:
取消
9
0
1
0