- 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 get important information about various events that occur in the SQL Server instance without having to enable any controls or auditing, just by reading the information already collected by the standard SQL Server trace.
Although I have already talked about this in some other posts, I decided to close this subject and make a post with several more examples of use.
The standard SQL trace collects 34 different types of events, which can be manually generated by tools such as SQL Server Profiler or with Transact-SQL queries.
The examples below show how to get information from different types of events, such as Shrink, AutoGrow, Automatic Statistics Update, Backup / Restore, DBCC, No Column Statistics, among others.
An important point to note is that this default SQL Server trace runs in the background by default, collecting only a few events and therefore has minimal impact on the SQL Server instance.
By enabling a trace using SQL Server Profiler, which displays real-time results through the interface, you can end up with impacts such as performance degradation in the environment, especially if you don't filter out just the events you want to observe and what criteria will be set for you. identify the sessions that will be part of Trace.
Map default SQL Server
Normally, we have running on our SQL Server instance only the default trace, which is enabled by default, although in some environments you may find more than one trace running if any DBA creates a custom trace.
Listing active traces on instance
To list the active traces in the instance, you can use the query below:
| 1 | SELECT * FROM sys.traces |
Identifying the default trace
Although the default trace is usually the trace with ID = 1, this is not always the case. Therefore, I recommend using the query below to identify the default trace:
| 1 | SELECT * FROM sys.traces WHERE is_default = 1 |
Listing the default trace events
As noted above, the default SQL Server trace collects different 34 events. To identify the full list of events, simply execute this query:
| 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) EI JOIN sys.trace_events TE ON EI.eventid = TE.trace_event_id |
Enabling Default Trace (Already Enabled After Installation)
To enable SQL Server's default trace is quite simple, just run sp_configure:
| 1 2 3 4 5 6 7 8 9 10 11 12 | EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'default trace enabled', 1; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE; GO |
Disabling Default Trace
To disable SQL Server default tracing is as simple as enabling. Just run sp_configure:
| 1 2 3 4 5 6 7 8 9 10 11 12 | EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'default trace enabled', 0; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE; GO |
Identifying the events
A simple way to identify events that occurred in the instance is by running the query below:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT B.trace_event_id, B.name AS EventName, A.DatabaseName, A.ApplicationName, A.LoginName, COUNT(*) AS Quantity FROM sys.fn_trace_gettable(@path, DEFAULT) A INNER JOIN sys.trace_events B ON A.EventClass = B.trace_event_id GROUP BY B.trace_event_id, B.name, A.DatabaseName, A.ApplicationName, A.LoginName ORDER BY B.name, A.DatabaseName, A.ApplicationName, A.LoginName |
Identifying Autogrowth Events
One of the most important events that we can observe with the default trace is Autogrowth events, which occur when this option is enabled in the database and it reaches full size, needing to allocate more disk space.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT DatabaseName, [FileName], CASE EventClass WHEN 92 THEN 'Data File Auto Grow' WHEN 93 THEN 'Log File Auto Grow' END AS EventClass, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 92, 93 ) ORDER BY StartTime DESC |
Identifying Disk Shrink Events
Another really cool event to monitor is the Disk Shrink event, which occurs when the database is allocating more space than it is using and the DBA reallocates the allocated space, resulting in free filesystem space.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 116 ) AND TextData LIKE 'DBCC%SHRINK%' ORDER BY StartTime DESC |
Identifying When DBCC Commands were Executed on Instance
Very useful audit to identify who executed or when a DBCC command was executed on instance
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 116 ) ORDER BY StartTime DESC |
Identifying when backups were performed
Another very interesting audit for DBA, which makes it possible to identify the date and who ran the backups on the instance (which can also be found in the msdb.dbo.backupset table).
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT DatabaseName, TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 115 ) AND EventSubClass = 1 ORDER BY StartTime DESC |
Identifying When Backups Were Restored
Interesting audit that allows you to identify the date and which user performed a restore on the instance (which can also be queried in the msdb.dbo.restorehistory, msdb.dbo.restorefile and msdb.dbo.restorefilegroup tables)
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 115 ) AND EventSubClass = 2 ORDER BY StartTime DESC |
These are just a few examples of what the default trace returns to us. There is the extensive list of events for you to explore!
Good luck, hug and until the next post!
Good morning Dirceu! I'm doing a test by writing the direct trace of the Profiler tool, and also writing the file trace to the table using fn_trace_gettable. Using the function, I am unable to write Rownumber. Can you tell me how to record this field? I noticed that there are other fields that the function does not record, but these are not important to me at the moment.
Thank you
Cassio, I don't remember seeing this .. Have you ever solved this?
If not, tell me that I try to simulate this scenario and tell you
Dirceu, congratulations on the post. It helped me a lot and opened my vision to the possibilities of auditing in BD.