信息来源: 邪恶八进制信息安全团队
SQL SERVER 2005中，新增加了许多新的特性，其中的DDL触发器是个不错的选择，根据资料初步学习如下，现整理之：
在sql server 2000中，只能为针对表发出的 DML 语句（INSERT、UPDATE 和 DELETE）定义 AFTER 触发器。SQL Server 2005 可以就整个服务器或数据库的某个范围为 DDL 事件定义触发器。可以为单个 DDL 语句（例如，CREATE_TABLE）或者为一组语句（例如，DDL_DATABASE_LEVEL_EVENTS）定义 DDL 触发器。在该触发器内部，您可以通过访问 eventdata() 函数获得与激发该触发器的事件有关的数据。该函数返回有关事件的 XML 数据。每个事件的架构都继承了 Server Events 基础架构。
比如，在SQL SERVER 2005中，建立一个叫DDLTrTest 的数据库，并且建立一个叫mytable的表
和Usp_Querymytable 的存储过程，如下所示
DROPDATABASE[DDLTRTEST]
GO
CREATEDATABASE DDLTRTEST
GO
USE[DDLTRTEST]
GO
IFEXISTS (SELECT*FROM SYS.OBJECTS
WHEREOBJECT_ID=OBJECT_ID(N'[DBO].[MYTABLE]')
AND TYPE IN (N'U'))
DROPTABLE[DBO].[MYTABLE]
GO
CREATETABLE MYTABLE(ID INT, NAME VARCHAR(100))
GO
INSERTINTO MYTABLE SELECT1,'A'
INSERTINTO MYTABLE SELECT2,'B'
INSERTINTO MYTABLE SELECT3,'C'
INSERTINTO MYTABLE SELECT4,'D'
INSERTINTO MYTABLE SELECT5,'E'
INSERTINTO MYTABLE SELECT6,'F'
GO
USE[DDLTrTest]
GO
IFEXISTS (SELECT*FROM sys.objects WHEREobject_id=
OBJECT_ID(N'[dbo].[usp_querymytable]')
AND type in (N'P', N'PC'))
DROPPROCEDURE[dbo].[usp_querymytable]
GO
CREATEPROC USP_QUERYMYTABLE
AS
SELECT*FROM MYTABLE
GO
CREATETRIGGER STOP_DDL_on_Table_and_PROC
ONDATABASE
FOR CREATE_TABLE,DROP_TABLE,
ALTER_TABLE,CREATE_PROCEDURE,
ALTER_PROCEDURE,DROP_PROCEDURE
AS
SELECT EVENTDATA().value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)')
PRINT'You are not allowed to CREATE,ALTER and DROP
any Tables and Procedures'
ROLLBACK;
ALTER TABLE MYTABLE ADD X INT
ALTERTABLE MYTABLE ADD X INT
(1 row(s) affected)
You are not allowed toCREATE,ALTERandDROPany Tables and Procedures
Msg 3609, Level16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
DROPTABLE MYTABLE
(1 row(s) affected)
You are not allowed toCREATE,ALTERandDROPany Tables and Procedures
Msg 3609, Level16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted
因为我们的触发器规定了不能使用CREATE_TABLE、DROP_TABLE、ALTER_TABLE、CREATE_PROCEDURE、ALTER_PROCEDURE、DROP_PROCEDURE等操作。
DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC ON DATABASE
当然，我们要对整个服务器采取策略的话，也是很简单的，和上面的方法大致相同只不过将on database的参数改为on server,比如
CREATETRIGGER STOP_DDL_on_Table_and_PROC
ONALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE
AS
PRINT'You are not allowed to CREATE,ALTER and DROP any Databases'
ROLLBACK;
