In this article, we will un-riddle the ways to make use of the data definition language trigger (DDL Trigger), in order to monitor the progressions made to the database programming objects, View, Procedure or Function with a few real-time examples.

Why DDL Triggers?

SQL Server DDL triggers are specifically used to control and review the changes taking place in the database. These triggers can be used to put the limit on the unauthorized clients to make DDL type of changes such as DROP VIEW, DROP PROCEDURE, DROP Function and so on using DDL Trigger. These triggers can also give permission to be able to make changes on specific database objects during a pre-decided time frames. The “Audit” is a majorly used for the implementation purpose of DDL triggers in the SQL Server. The Object Schema changes audit helps to follow who has performed the DDL proclamation. For instance, if we are keen on distinction who has dropped the Object or who has made changes to the Objects. The DDL triggers will be executed because as the Transact-SQL event all set on the database or on the server with characterize ON ALL SERVER or ON DATABASE. Here we need to know that the extent of the trigger depends upon the event.

How DDL Trigger Works?

Every DDL operation generates one Transaction in case of the DDL Trigger have been applied at the Database or the Server level. The SQL Server generates the events with relevant information in the same transaction following the operation. Prepare a metric with extracting the DDL event function(EVENTDATA()) to wraps a policy or standards for deployment:

The EVENTDATA() is an inbuilt function of the DDL trigger in SQL Server and that would return exchange occasion subtleties with the number of the fields in XML format

• EventType (Create View, Alter View, Drop View, etc…)
• PostTime (Event trigger time)
• SPID (SQL Server session ID)
• ServerName (SQL Server instance name)
• LoginName (SQL Server Login name)
• UserName (username for login, by default dbo schema as username)
• DatabaseName (name of database where trigger was executed)
• SchemaName (schema name of the View)
• ObjectName (Name of the View)
• ObjectType (Object types. such as Table, view, procedure, etc…)
• TSQLCommand (Schema deployment Query which is executed by user)
• SetOptions (SET Option which are applied while Creating View or Modify it)
• CommandText (Create, Alter or Drop object command)

EVENTDATA() returns multiple fields in XML format as shown above and using those fields, we are able to create such metrics to track various events of DDL over the objects. In general, each DDL event of the object schema changes can be appended into the table, these event types are mentioned in the header body of ä trigger with the FOR CREATE_, ALTER_, DROP_,…

Trigger:

View Script:

Using the above trigger for Creating, Altering or Dropping the View, Function or procedure, the transactions that were finished successfully or not can be monitored. Furthermore, at this moment we can check the event_object_data table to get the latest event data. We can see here that each detail of the above transaction has been included in the XML design:

In the above instance, we have rightfully sent out of the XML in the table. Despite that, in one of the correct methods for checking that XML has to be pulled out in the Column arrangement of the table. Talking of the same, underneath the XML command can assist with gathering the required columns of the event data.

Here, @xml is a EVENTDATA() with in a trigger only.

Authorize Login to Deploy Objects using DDL

The Development or the QA Server does not require restricting execution of the procedure or any other programming objects; however, on the production server, sometimes the client does not have access to some contents of the table or the associated database objects. The procedure above will allow getting executed from the application itself and no one can alter or drop the objects apart from an authorized login or authorized member of the designated role; hence, in a few certain situations, the confidential object can also be restricted in order to secure the privacy of the client’s data.

The Table and Information access can be handled with the help of different methodologies in SQL Server. However, for programming object schema changes, we are required to manage user and role policies to allow members to perform CREATE, ALTER and DROP operations (DDL). The DDL trigger allows us to fiddle with a particular type of object to authorized members and roles in the SQL Server.

Most of the companies manage the release code branch to track the schema changes in the SQL Server to track down the information like, who, when and for what situation applied? In order to make it easy to get some reports or get details in tabular ways, the event data can be extracted to the table in a database. As the earlier mentioned EVENTDATA() returns each detail of transactions with the type of event with an object.

Event type can be differentiated by XML to supervise DDL events over the objects. As an example, authorized role members will only make schema changes or DROP afterward. Now using the below DDL Trigger we will be able to make authorization wrapper over the objects:

In the above Trigger we have validated it using DDL with the condition that If the Object Type is ‘VIEW’ and the Object Name is ‘vw_roles’ and the Logged in user is ‘myel’ then DDL trigger audit_objects will not permit to make changes and the user will be acknowledged with the error message:

As can be seen, the user gets an error on altering the view and event_object_data will get inserted as above with the event data. In the above example, we have one object name only in condition but that could be combined with multiple objects or the object types and user login as well.

In the above example, myel is not allowed to ALTER_VIEW vw_roles. The message gets logged and roll-back the transaction is performed. Simultaneously event data will get inserted in event_object_data table in the master database in XML format as shown below:

The database administrator can drop or modify the trigger within SSMS as well, however, the user needs to have permissions to execute that action:

As a contraposition to this, the database authorized operators should get an alert in case a user who is trying to deploy the schema changes however the user is not permitted to deploy. Now to avoid this circumstance and to follow an add-on step, in a certain diverted way, a mail alert will be triggered inside the same DDL trigger with essential information. Anyhow, event data will get exported into the table to get detailed information about the occurrence; however, an alert that makes sense to keep an eye on the database as part of the security monitor:

Finally, the subject can be obtained with the name of the object, object type, database name and server name for just simplicity to identify the mail alert. The recipients should be part of the group mail address of the responsible database administrator group. In this article code snippet, we explained views only, however, it can be other database objects as well like: procedures and functions mentioned in the DDL trigger header.

Table of contents

• Author
• Recent Posts

Jignesh Raiyani

Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.

View all posts by Jignesh Raiyani

Latest posts by Jignesh Raiyani (see all)

• Page Life Expectancy (PLE) in SQL Server - July 17, 2020
• How to automate Table Partitioning in SQL Server - July 7, 2020
• Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020