Find user activity without any Auditing

694 views 20:31 0 Comments 28 October 2019

How to find out user activity on database without any particular Auditing

Sometime you may need to find out who ran some script or who is accessing the database or who made some modification or deleted some data or updated some data. Even you will get the host name from where the query was executed and what time the query was started to execute.

You don’t have anything database auditing setup or trace flag enables. In that case, you can make use of the default trace running on the SQL instance.

Step 1: Find out the default trace running on the server.

select *from ::fn_trace_getinfo(default)
Find SQL Trace
Find SQL Trace

Step 2: Run the below query to find out the user activity on the databases. If you see the 1st result, you can see  I was running a select statement using SPID 57, that got captured. Similar way if an application or any user run any script against a database that will get captured. In this way, you may find out who has run some drop, delete, update or insert command. It also captured if someone run executes any stored procedure.

If you don’t find anything you expected on this file , you may try to use some old file. To read the old file you will need to just change the file name such as log_87, log_86. In this way you can read some old file.  Else you can physically check how many files are present in the default log location and then you can read the file using below command.

select 
DatabaseName as [Database Name]
,ApplicationName as [App Name]
,HostName as [Host Name]
,LoginName as[Login Name]
,SPID
,StartTime as [Start Time]
,EndTime as [End Time]
,TextData as [Query executed]
from::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\log_88.trc',5)
Order by 6 DESC
SQL Trace Output
SQL Trace Output

Leave a Reply

Your email address will not be published. Required fields are marked *