Sometimes, a database has multiple accounts, including database administrators, developers, operations support personnel, and so on, there may be many accounts have relatively large permissions, such as the DDL operation Rights (create, modify, delete stored procedures, create, modify, delete tables, etc.), the account is more, management will be quite troublesome, Easily confusing, if the database administrator does not monitor database schema changes, do not know who changes to the database schema (here to change only DDL operations), especially sometimes, some developers may not follow the rules, bypassing or forgetting to notify the publisher or DBA, Directly to the production machine to do some DDL operations, then we need to monitor some changes in the database schema, if we can monitor and leave the evidence, so that the DBA or related managers aware of these changes, effectively manage the database, can also avoid problems, the phenomenon of wrangling, At last the DBA became the scapegoat.
Here is a solution to this problem by creating a table DatabaseLog and DDL triggers to solve the problem, starting with a new table DatabaseLog in the msdb database to hold the information that DDL triggers get. Where DDL triggers primarily return information about server or database events through the EVENTDATA () function.
Copy Code code 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 = out) 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 of the DDL change occurred. ', @level0typ E=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 is executed. ', @level0t Ype=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. ', @leve L0type=n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' DatabaseLog ', @level2type =n ' COLUMN ', @ Level2name=n ' Schema '
Go
EXEC sys.sp_addextendedproperty@name=n ' ms_description ', @value =n ' The object that is changed by the DDL statment. ', @leve L0type=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 is 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. ', @leve L0type=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 Databas E. Data is captured by the database trigger Ddldatabasetriggerlog. ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1ty Pe=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, I want to monitor the DDL operation of the database myassistant, then we first create a configuration file named "Database_ddl_event" in "Database Mail" (profile name), which is not much said, not known configuration, I first practice practicing, if I need to let the database monitor to DDL operation changes believe that the information sent to my mailbox *****@***.com (with your own mailbox replacement), then only need to modify the following code mailbox and Profile_name can.
Copy Code code 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 ' N ' <table border= "0" > ' +
N ' <tr><th>post time</th><th>user</th><th>login</th><th> Clienthost</th> ' +
N ' <th>TSQL</th><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
And then we'll test it, if a user test logs in to the database and accidentally deletes a test table, as shown in figure one, I will receive an email indicating what DDL operations the user test performed on that client host (as shown in figure II below), and of course the style of the message, The layout is interesting to be able to beautify.
