禁止修改数据库的数据表示例

/* 利用触发器禁止修改一些特殊的数据表 */


CREATE TRIGGER Table_Monitor ON DATABASE

FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE

 

AS

Declare @S Varchar(800)

Set @S=EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,‘nvarchar(max)‘)

Print @S

 

Create Table dbo.Test2(ID Int Not Null) --创建表

Go

Alter Table dbo.Test2 Add Name Varchar(20) --添加字段

Go

Alter Table dbo.Test2 Alter Column Name Varchar(40) --修改字段

Go

Alter Table dbo.Test2 Add Constraint PK_Test2 Primary Key (ID) --添加主键

Go

Alter Table dbo.Test2 Drop Column Name --删除字段

Go

 

Drop Table dbo.Test2 --删除测试表

 

--监控表,若名为表,若名为表,若名为Test2Test2Test2Test2Test2Test3Test3Test3Test3Test3

--则取消操作则取消操作则取消操作

 

Create TRIGGER Table_Monitor_Deny ONDATABASE

FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE

AS

Declare @S Varchar(800)

Set @S=EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,‘nvarchar(max)‘)

Set @S=SubString(@S,CharIndex(‘Table‘,@S)+6,200) --截取关键词Table后面的字符串,以准备取表名

If CharIndex(‘ ‘,@S)>0

Set @S=SubString(@S,1,CharIndex(‘ ‘,@S)-1) --截取表名,若表名含有空格,则会误判

If CharIndex(‘.‘,@S)>0 --若含. 通常是方案名, dbo.Test2

Set @S=SubString(@S,CharIndex(‘.‘,@S)+1,200)

Set @S=Replace(@S,‘[‘,‘‘) --表名可能以[]界定表示,如dbo.[Test2]

Set @S=Replace(@S,‘]‘,‘‘)

If CharIndex(@S,‘Test2,Test3,‘)>0 --若为Test2Test3,则取消操作

Begin

Set @S=表:+@S+禁止任何DDL 操作

RaisError(@S,10,1)

Rollback

End

--创建后,再创建表名为Test2(或已有表Test2,对其修改、删除表结构),就会返回错误

--删除、删除DDLDDL 触发器触发器

Drop Trigger Table_Monitor On Database

Drop Trigger Table_Monitor_Deny OnDatabase

 

 

--创建服务器级DDLDDL 触发器,此处以登录为例触发器,此处以登录为例触发器

Use Master

Go

--创建日志表

Create Table dbo.SysLog(_Date DateTime,Remark Varchar(800))

Go

--创建测试登录帐号

Create Login LoginTest With Password=‘qwe123!@#‘

--创建测试用户

Create User LoginUser For Login LoginTest

--授予插入权限,否则以LoginTest登录时会报错

Grant Insert To LoginUser

Go

Create TRIGGER Logon_Monitor ON All SERVER

FOR LOGON

AS

Set NoCount On

Declare @S Varchar(800)

Select @S=登录时间:+Convert(Char(19),Login_Time,121)+电脑名:+Host_Name

+登录名:+Login_Name+‘ SPID+Cast(@@SPID as Varchar(10))

From Sys.dm_exec_sessions Where session_id=@@SPID

Insert Master.dbo.SysLog(_Date,Remark) Values (GetDate(),@S)

--开启SSMSSQL登录帐号LoginTestsaWindows管理员帐号登录SQL SERVER,再执行查询验证效果:

Select _Date,Remark From dbo.SysLog order By _Date Desc

--删除服务器级删除服务器级删除服务器级DDLDDL 触发器

Drop Trigger LogOn_Monitor On All Server


本文出自 “畅想天空” 博客,请务必保留此出处http://kinwar.blog.51cto.com/3723399/1549072

禁止修改数据库的数据表示例

上一篇:Win10升级后VMWare内的系统连不了网如何解决


下一篇:【转】《分享一下我研究SQLSERVER以来收集的笔记》未整理