DDL operations of MSSQL monitoring database

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.

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.

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 them. U.S. servers are prone 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, therefore, we need to monitor some changes to the database architecture and virtual hosts. If we can monitor and leave evidence, we can let DBAs or related administrators know the changes, effective database management can also avoid problems, such as spof, website space, and DBA.

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.

SQL Code 1

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.

SQL Code 2

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.