触发器系列一之DDL触发器

近期出于同步数据的需求,需要创建一个具有在目标数据库具有alter table权限的帐号,但是由于该工具在alter table同步表结构时会同时频繁发起create index和drop index。现阶段工具无法优化,只能从权限入手直接收回alter index权限。sqlserver的alter权限包含alter,create,drop,无法单独赋予create或drop权限。想让帐号只能...
摘要由CSDN通过智能技术生成

近期出于同步数据的需求,需要创建一个具有在目标数据库具有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
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值