How to Identify Who Executed a Backend UPDATE Query in SQL Server?

[#1]

Hi Experts,

I have a question regarding SQL Server.

In our environment, users sometimes execute backend SQL queries (such as UPDATE statements) directly against the database to modify data.

I would like to know:

• Is it possible to identify who executed the UPDATE query?
• Can I find the exact date and time when the query was executed?
• Is there any way to view the actual UPDATE statement that was run?
• If SQL Server Audit or any auditing mechanism was not enabled before the change, is it still possible to retrieve this information from the transaction log or any other source?

I would appreciate your guidance on the available methods and best practices for tracking backend database modifications.

Thank you.

[markus_schaefer242]

Hi, 
try to find out, if your Users do these Update via SAP B1 Query-Manager.
here is a authorization where you can block it for all non administratiors 

to log these Queries:
--> System ini
--> General Settings
--> Services

if is active, you can try CGEV = Event Log

select * FROM dbo.[CGEV]

  Attention: please try with care... Due to the massive amount of data (each query execution is logged)

[raajj_8-1]

I think SQL Profiler can help you on this.

[inga_babco2]

Are the update queries executed directly in SAP Business One or in SQL Server Management Studio? If in SAP Business One, then there is a standard solution available.
If the checkbox is selected in the General Settings (Enable Execution Audit Log for User-Defined Query or Query Generator), SAP logs all queries executed by users in table CGEV.

You can then search for UPDATE queries using the following query:

SELECT * from CGEV
WHERE EventDetls like '%update %'

Here you can see who ran this query and when.

[Imman28]

Hi, as per the details you shared, we can only see the updated query execution in SAP Query Manager on the front end. However, I need to know who updated the query in SQL Server Management Studio.

[raajj_8-1]

You can't find that from front end, try to use the SQL profiler and keep it running with scenarios so you can track the future cases too.