SQL Server数据库级别触发器

禁止修改表结构和加表

 CREATE TRIGGER [Object_Change_Trigger_DDL] ON DATABASE

FOR ALTER_TABLE,DROP_TABLE,CREATE_TABLE,CREATE_INDEX,ALTER_INDEX, DROP_INDEX  
AS
DECLARE @EventData AS XML;
SELECT @EventData = EVENTDATA();
IF @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(150)') NOT IN (
'uws_M_ApricotMDM_dev'
,'Us_wangdan_temp'
,'NT AUTHORITY\SYSTEM'
,'NT SERVICE\MSSQLSERVER'
,'WIN-6RNHUPNK4OJ\Administrator'
,'NT SERVICE\SQLSERVERAGENT'
,'bl_un'
) 
  BEGIN
   --RAISERROR ('创建，修改，删除表的权限已收回，如有问题请联系DBA!', 16, 1)     
   ROLLBACK
  END

GO
ENABLE TRIGGER [Object_Change_Trigger_DDL] ON DATABASE
GO

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE  TABLE  [dbo].[DDLMonitor](      [ID] [ INT ] IDENTITY(1,1)  NOT  NULL ,      [SPID] [ INT ]  NULL ,      [ServerName] [ VARCHAR ](150)  NULL ,      [PostTime] [DATETIME]  NULL ,      [EventType] [ VARCHAR ](300)  NULL ,      [LoginName] [ VARCHAR ](150)  NULL ,      [UserName] [ VARCHAR ](100)  NULL ,      [SchemaName] [ VARCHAR ](100)  NULL ,      [DatabaseName] [ VARCHAR ](100)  NULL ,      [ObjectName] [ VARCHAR ](100)  NULL ,      [ObjectType] [ VARCHAR ](100)  NULL ,      [TSQLCommand] [ VARCHAR ]( MAX )  NULL ,      [EventData] [XML]  NULL ,      [createdate] [DATETIME]  NULL  DEFAULT  (GETDATE()),   CONSTRAINT  [PK_DDLMonitor]  PRIMARY  KEY  NONCLUSTERED (      [ID]  ASC ) WITH  (PAD_INDEX =  OFF , STATISTICS_NORECOMPUTE =  OFF , IGNORE_DUP_KEY =  OFF , ALLOW_ROW_LOCKS =  ON , ALLOW_PAGE_LOCKS =  ON )  ON  [ PRIMARY ] )  ON  [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]   GO

--记录加的表和字段

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

CREATE  TRIGGER  [trg_DDL_audit]  ON  DATABASE FOR  ALTER_PROCEDURE,DROP_PROCEDURE,ALTER_FUNCTION,DROP_FUNCTION,ALTER_TABLE,DROP_TABLE,CREATE_TABLE AS   DECLARE  @EventData  AS  XML; SELECT  @EventData = EVENTDATA();   INSERT  INTO  DDLMonitor.dbo.DDLMonitor( SPID, ServerName, PostTime, EventType, LoginName, UserName, SchemaName, DatabaseName, ObjectName, ObjectType, TSQLCommand, [EventData] ) VALUES ( @EventData.value( '(/EVENT_INSTANCE/SPID)[1]' , 'int' ), @EventData.value( '(/EVENT_INSTANCE/ServerName)[1]' , 'varchar(50)' ), @EventData.value( '(/EVENT_INSTANCE/PostTime)[1]' , 'datetime' ), @EventData.value( '(/EVENT_INSTANCE/EventType)[1]' , 'varchar(100)' ), @EventData.value( '(/EVENT_INSTANCE/LoginName)[1]' , 'varchar(150)' ), @EventData.value( '(/EVENT_INSTANCE/UserName)[1]' , 'varchar(100)' ), @EventData.value( '(/EVENT_INSTANCE/SchemaName)[1]' , 'varchar(100)' ), @EventData.value( '(/EVENT_INSTANCE/DatabaseName)[1]' , 'varchar(100)' ), @EventData.value( '(/EVENT_INSTANCE/ObjectName)[1]' , 'varchar(100)' ), @EventData.value( '(/EVENT_INSTANCE/ObjectType)[1]' , 'varchar(100)' ), @EventData.value( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]' , 'varchar(max)' ), @EventData ) GO   ENABLE  TRIGGER  [trg_DDL_audit]  ON  DATABASE GO

 

 --禁用当前数据库中所有数据库级别的 DDL 触发器：
  DISABLE TRIGGER ALL ON DATABASE
  --禁用服务器实例中所有服务器级别的 DDL 触发器：
  DISABLE TRIGGER ALL ON ALL SERVER

  

  SELECT * FROM sys.server_triggers

  ENABLE Trigger ALL ON ALL SERVER;

 

转载于:https://www.cnblogs.com/weifeng123/p/9916580.html