- Audit on SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement audit and control of logins (Logon Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)
- SQL Server - Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
- SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes to tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to Avoid Brute Force Attacks on Your Database
- SQL Server Security Checklist - An SP with more than 70 security items to validate your database.
- SQL Server - How to know the date of a user's last login
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
- SQL Server – Beware of server role securityadmin! Using privilege elevation to become sysadmin
- SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.
- SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts
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:
| 1 2 3 4 5 6 7 8 | DECLARE @id INT = ( SELECT id FROM sys.traces WHERE is_default = 1 ) SELECT DISTINCT eventid, name FROM fn_trace_geteventinfo(@id) A JOIN sys.trace_events B ON A.eventid = B.trace_event_id |
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.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | DECLARE @Ds_Arquivo_Trace VARCHAR(255) = (SELECT SUBSTRING([path], 0, LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1) SELECT A.HostName, A.ApplicationName, A.NTUserName, A.NTDomainName, A.LoginName, A.SPID, A.EventClass, B.name, A.EventSubClass, A.TextData, A.StartTime, A.ObjectName, A.DatabaseName, A.TargetLoginName, A.TargetUserName FROM [fn_trace_gettable](@Ds_Arquivo_Trace, DEFAULT) A JOIN master.sys.trace_events B ON A.EventClass = B.trace_event_id WHERE A.EventClass IN ( 164, 46, 47, 108, 110, 152 ) AND A.StartTime >= GETDATE()-7 AND A.LoginName NOT IN ( 'NT AUTHORITY\NETWORK SERVICE' ) AND A.LoginName NOT LIKE '%SQLTELEMETRY$%' AND A.DatabaseName != 'tempdb' AND NOT (B.name LIKE 'Object:%' AND A.ObjectName IS NULL ) AND NOT (A.ApplicationName LIKE 'Red Gate%' OR A.ApplicationName LIKE '%Intellisense%' OR A.ApplicationName = 'DacFx Deploy') ORDER BY StartTime DESC |
That's it folks!
Thanks for stopping by and see you in the next post.
