SQLSERVER DLL监控

最新推荐文章于 2025-03-25 14:04:10 发布

原创最新推荐文章于 2025-03-25 14:04:10 发布 · 314 阅读

0 ·

CC 4.0 BY-SA版权

SQLServer经验总结专栏收录该内容

29 篇文章

订阅专栏

本文详细介绍了在SQL Server中创建和修改DDL触发器的过程，包括如何通过触发器记录数据库的所有DDL操作，并将相关信息存储到指定表中，以便于后续的审计和监控。文章还展示了如何扩展触发器功能，增加更多元的数据捕获能力。

USE [master]
GO

/****** Object: Table [dbo].[server_eventdata] Script Date: 2018/11/16 10:34:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[server_eventdata](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [eventdata] [xml] NULL,
   [principal_user] [nvarchar](100) NULL,
   [login_user] [nvarchar](100) NULL,
   [getdated] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
   [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]

USE [master]
GO

/****** Object: DdlTrigger [gyy_server] Script Date: 2018/11/16 10:45:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE trigger [gyy_server]
on all server
for create_table,
alter_table,
drop_table ,
alter_procedure,
create_procedure,
drop_procedure,
alter_index,
create_index,
drop_index
as

insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME() ,GETDATE()

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [gyy_server] ON ALL SERVER
GO

----------------修改

USE [master]
GO
alter trigger [gyy_server]
on all server
for create_table,
alter_table,
drop_table ,
alter_procedure,
create_procedure,
drop_procedure,
alter_index,
create_index,
drop_index
as

insert into server_eventdata ([eventdata]
,[principal_user]
,[login_user]
,[getdated])
select EVENTDATA(),USER,SUSER_NAME() ,GETDATE()

alter table master.dbo.server_eventdata add hostname varchar(100) null
go
alter table master.dbo.server_eventdata add PROGRAMNAME varchar(100) null

GO
alter trigger [gyy_server]
on all server
for create_table,
alter_table,
drop_table ,
alter_procedure,
create_procedure,
drop_procedure,
alter_index,
create_index,
drop_index
as

insert into server_eventdata ([eventdata]
,[principal_user]
,[login_user]
,[getdated],[hostname]
,[PROGRAMNAME])
select EVENTDATA(),USER,SUSER_NAME() ,GETDATE(),host_name(),PROGRAM_NAME()