MSSQL monitors database DDL operations (create, modify, delete stored procedures, create, modify, and delete tables)

Sometimes, a database has multiple accounts, including database administrators, developers, and O & M support personnel. Many accounts may have relatively high permissions, for example, DDL operation permissions (creation, modification, deletion of stored procedures, creation, modification, and deletion of tables). If there are too many accounts, it will be quite troublesome to manage and lead to confusion, if the Database Administrator does not monitor database architecture changes, he or she does not know who has made any changes to the database architecture (only DDL operations are involved here). In particular, sometimes, some developers may not follow the rules and regulations, bypass or forget to notify publishers or DBAs, and directly go to the production machine for some DDL operations, then, we need to monitor some changes to the database architecture. If we can monitor and leave evidence, this will allow DBAs or related management personnel to know these changes and effectively manage the database, it can also avoid problems, so that DBAs become a black box.

The following is a solution to the above problem. We create a table DatabaseLog and DDL trigger to solve the problem. First, we create a table DatabaseLog In the msdb database to save the information obtained by the DDL trigger. DDL triggers mainly use the EVENTDATA () function to return information about server or database events.

Copy codeThe Code is as follows:
USE msdb;
GO
Create table [dbo]. [DatabaseLog]
(
[DatabaseLogID] [int] IDENTITY (1, 1) not null,
[PostTime] [datetime] not null,
[DatabaseUser] [sysname] COLLATE SQL _Latin1_General_CP1_CI_AS NOT NULL,
[LoginName] [sysname] COLLATE SQL _Latin1_General_CP1_CI_AS NULL,
[ClientHost] [sysname] COLLATE SQL _Latin1_General_CP1_CI_AS NULL,
[Event] [sysname] COLLATE SQL _Latin1_General_CP1_CI_AS NOT NULL,
[Schema] [sysname] COLLATE SQL _Latin1_General_CP1_CI_AS NULL,
[Object] [sysname] COLLATE SQL _Latin1_General_CP1_CI_AS NULL,
[TSQL] [nvarchar] (max) COLLATE SQL _Latin1_General_CP1_CI_AS NOT NULL,
[XmlEvent] [xml] not null,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
[DatabaseLogID] ASC
) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' Primary key for DatabaseLog records. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n' COLUMN ', @ level2name = n' databaselogid'
GO
EXEC sys. sp_addextendedproperty @ name = N 'Ms _ description', @ value = n' The date and time the DDL change occurred. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n'column', @ level2name = n'posttime'
GO
EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' The user who implemented the DDL change. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n' COLUMN ', @ level2name = n' databaseuser'
GO
EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' The login which implemented the DDL change. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n'column', @ level2name = n'loginname'
GO
EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' The client machine on which implemented the DDL change. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n'column', @ level2name = n'clienthost'
GO
EXEC sys. sp_addextendedproperty @ name = N 'Ms _ description', @ value = n' The type of DDL statement that was executed. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n'column', @ level2name = n'event'
GO
EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' The schema to which the changed object belongs. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n'column', @ level2name = n'scheme'
GO
EXEC sys. sp_addextendedproperty @ name = N 'Ms _ description', @ value = n' The object that was changed by the DDL statment. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = N 'column ', @ level2name = N 'object'
GO
EXEC sys. sp_addextendedproperty @ name = N 'Ms _ description', @ value = n' The exact Transact-SQL statement that was executed. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n' COLUMN ', @ level2name = n' tsql'
GO
EXEC sys. sp_addextendedproperty @ name = N 'Ms _ description', @ value = n' The raw XML data generated by database trigger. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog ', @ level2type = n'column', @ level2name = n'xmlevent'
GO
EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' Audit table tracking all DDL changes made to the database. data is captured by the database trigger ddlDatabaseTriggerLog. ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog'
GO
EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' Primary key (nonclustered) constraint ', @ level0type = n' SCHEMA', @ level0name = n' dbo ', @ level1type = n' TABLE ', @ level1name = n' DatabaseLog', @ level2type = n' CONSTRAINT ', @ level2name = n' PK _ DatabaseLog_DatabaseLogID'
GO

For example, if I want to monitor the DDL operations of the database MyAssistant, first create a profile name in "database mail" with the configuration name "DataBase_DDL_Event, if you do not know the configuration, you should first practice it. If I want the database to send the information that monitors DDL operation changes to my mailbox *****@***. com (replaced by your own mailbox), you only need to modify the email address and profile_name of the code below.

Copy codeThe Code is as follows:
USE MyAssistant;
GO

Create trigger [DTG_DatabaseDdlTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
Set nocount on;

DECLARE @ data XML;
DECLARE @ schema sysname;
DECLARE @ object sysname;
DECLARE @ eventType sysname;
DECLARE @ tableHTML NVARCHAR (MAX );

SET @ data = EVENTDATA ();
SET @ eventType = @ data. value ('(/EVENT_INSTANCE/EventType) [1]', 'sysname ');
SET @ schema = @ data. value ('(/EVENT_INSTANCE/SchemaName) [1]', 'sysname ');
SET @ object = @ data. value ('(/EVENT_INSTANCE/ObjectName) [1]', 'sysname ')

IF @ object IS NOT NULL
PRINT ''+ @ eventType + '-' + @ schema + '.' + @ object;
ELSE
PRINT ''+ @ eventType + '-' + @ schema;

IF @ eventType IS NULL
Print convert (nvarchar (max), @ data );

INSERT [msdb]. [dbo]. [DatabaseLog]
(
[PostTime],
[DatabaseUser],
[LoginName],
[ClientHost],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE (),
CONVERT (sysname, CURRENT_USER ),
@ Data. value ('(/EVENT_INSTANCE/LoginName) [1]', 'nvarchar (max )'),
CONVERT (sysname, HOST_NAME ()),
@ EventType,
CONVERT (sysname, @ schema ),
CONVERT (sysname, @ object ),
@ Data. value ('(/EVENT_INSTANCE/TSQLCommand) [1]', 'nvarchar (max )'),
@ Data
);

SET @ tableHTML =
N' <H1> DDL Event </H1> '+
N' <table border = "0"> '+
N'< tr> <th> Post Time </th> <th> User </th> <th> Login </th> <th> ClientHost </th> '+
N'< th> TSQL </th> </tr> '+
CAST (SELECT
Td = PostTime ,'',
Td = DatabaseUser ,'',
Td = LoginName ,'',
Td = ClientHost ,'',
Td = TSQL ,''
FROM msdb. dbo. DatabaseLog
WHERE DatabaseLogID = (select max (DatabaseLogID) from msdb. dbo. DatabaseLog)
For xml path ('tr'), TYPE) as nvarchar (MAX) + N' </table> ';

EXEC msdb. dbo. sp_send_dbmail
@ Profile_name = 'database _ DDL_Event ',
@ Recipients = '***** @ ***. com ',
@ Subject = 'ddl Event-DataBase MyAssistant ',
@ Body = @ tableHTML,
@ Body_format = 'html ';
END;
GO

Next let's Test it. If a user Test accidentally deletes a Test table when logging on to the database, as shown in, I will receive an email, prompt me what DDL operations the user Test has performed on that client host (as shown in figure 2). Of course, if you are interested in the mail style and layout, You can beautify it.