SQLSERVER DLL监控

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]

GO

 

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() 

GO

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()

GO

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() 

GO