Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable

Views: 1.215
This post is the 6 part of 16 in the series. Security and Audit
Reading Time: 3 minutes

Hello people,
Good afternoon!

In this post I will demonstrate how to identify DDL and DCL events that occur on the SQL Server instance. This allows us to identify which user created / changed / deleted an object, or who added / removed a user to a system role or database role, and many other things without having to create any internal control or monitoring, using only the standard SQL Server trace, where we will query your data through the function fn_trace_gettable.

A while ago I posted the post here How to create an Audit trigger to log object manipulation in SQL Server that allowed us to create a server trigger that performs this audit. Although the fn_trace_gettable function returns much of the information available in this internal control, this trigger allows versioning of changed objects, such as procedures, functions, tables, etc. That is, if you want to automate the source versioning of objects in the database, the most appropriate post is the one above.

Remembering the concepts of DDL and DCL

I believe these concepts are already well known to most of you, but I will reinforce what each thing is for no doubt:

Data Definition Language (DDL) Operations
Used to define or change database structures or schemas.

CREATE - Used to create objects in the database.
ALTER - Changes the structure of an object.
DROP - Remove an object from the database.
TRUNCATE - Clears all records from a table, including allocated space.
COMMENT - Add comments on objects
RENAME - Renames an object

Data Control Language (DCL) Operations
Used to manage user permissions and privileges on certain SQL Server instance objects.

GRANT - Grant access to a user on a given object.
REVOKE - Remove user access on a given object.

Identifying trace event types

Using the query below, we can quickly identify all EventClass that we can use in the :: fn_trace_gettable function:

SQL Server - fn_trace_gettable EventClass

Identifying DDL and DCL Events on the SQL Server Instance

With the query below, you can identify who was the last person to change a particular object in the instance (Ex: Stored Procedure), who added a particular user to a role, who deleted a table, who executed a TRUNCATE command, etc, and all without having to create any controls other than those already available and enabled by default in SQL Server.

SQL Server - fn_trace_gettable Audit DDL DCL operations

That's it folks!
Thanks for stopping by and see you in the next post.