触发器系列一之DDL触发器

近期出于同步数据的需求，需要创建一个具有在目标数据库具有alter table权限的帐号，但是由于该工具在alter table同步表结构时会同时频繁发起create index和drop index。现阶段工具无法优化，只能从权限入手直接收回alter index权限。

sqlserver的alter权限包含alter,create,drop，无法单独赋予create或drop权限。想让帐号只能进行alter table操作，考虑创建Transact-SQL DDL触发器，当用户进行除alter table 外的其他操作，将回滚该操作并返回错误信息。

下面附上详细脚本与DDL触发器使用说明，后期会整理其他几种类型的触发器以作记录与学习。

创建测试帐号

--创建测试帐号
use master
go
create login t_test with pasword='test##123456';
use test 
go
create user t_test for login t_test
--赋予读写权限
EXEC sp_addrolemember  N'db_datareader',N'u_test';
EXEC sp_addrolemember  N'tb_datawriter',N'u_test';
--为该权限单独创建数据库角色
use test
go
CREATE ROLE tb_alter
--授予该数据库角色alter权限
GRANT alter TO tb_alter;
--赋予u_test帐号tb_alter角色身份
EXEC sp_addrolemember  N'tb_alter',N'u_test';

创建DDL触发器

alter TRIGGER TR_Limit_Role_tb_alter_DDL_Events
ON DATABASE
with execute as sa
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
  declare
    @eventData          XML,
    @DATABASENAME      SYSNAME,
    @EVENTDATE         DATETIME,
    @USERNAME          SYSNAME,
    @SYSTEMUSER        VARCHAR(128),
    @CURRENTUSER       VARCHAR(128),
    @ORIGINALUSER      VARCHAR(128),
    @HOSTNAME          VARCHAR